Oracle和MySQL查询本年已过去的月份

avatar 2022年08月21日22:01:09 0 136 views
博主分享免费Java教学视频,B站账号:Java刘哥

最近写了很多SQL,记录下

查询本年已过去的月份:如 2022-01、2022-02、...、2022-8

Oracle写法

SELECT to_char(sysdate, 'yyyy') || '-' || to_char(add_months(trunc(sysdate, 'yy'), rownum - 1), 'MM') AS TIME
FROM dual CONNECT BY rownum <= TO_NUMBER(TO_CHAR(sysdate, 'MM'))

MySQL写法

SELECT * FROM ( SELECT concat(( SELECT year(curdate()) ), '-01') AS TIME UNION SELECT concat(( SELECT year(curdate()) )
, '-02') UNION SELECT concat(( SELECT year(curdate()) ), '-03') UNION SELECT concat(( SELECT year(curdate()) ), '-04')
UNION SELECT concat(( SELECT year(curdate()) ), '-05') UNION SELECT concat(( SELECT year(curdate()) ), '-06') UNION
SELECT concat(( SELECT year(curdate()) ), '-07') UNION SELECT concat(( SELECT year(curdate()) ), '-08') UNION SELECT
concat(( SELECT year(curdate()) ), '
-09') UNION SELECT concat(( SELECT year(curdate()) ), '-10') UNION SELECT concat(( SELECT year(curdate()) ), '-11')
UNION SELECT concat(( SELECT year(curdate()) ), '-12') ) t2 WHERE STR_TO_DATE(CONCAT(TIME, '-01'), '%Y-%m-%d') <= (
SELECT curdate() FROM dual ) ORDER BY STR_TO_DATE(CONCAT(TIME, '-01'), '%Y-%m-%d')
  • 微信
  • 交流学习,有偿服务
  • weinxin
  • 博客/Java交流群
  • 资源分享,问题解决,技术交流。群号:590480292
  • weinxin
avatar

发表评论

avatar 登录者:匿名
可以匿名评论或者登录后台评论,评论回复后会有邮件通知

  

已通过评论:0   待审核评论数:0