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

言曌 2022年08月21日22:01:09 0 1210 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')