如何在数据集中直接生成一组数

比如我想要一个近三年的年月数据 如2024.07
2024.06
2024.06
...


写内嵌数据集或者导入Excel不够动态，因为每个月都要更新最新的月份
能不能直接在数据集里用语句生成一个嘞

试试这两个，我用gpt生成的

1. SELECT DISTINCT
   
2.     TO_VARCHAR(ADD_MONTHS(CURRENT_DATE, - n), 'YYYY.MM') AS YearMonth
   
3. FROM
   
4.     (
   
5.     SELECT
   
6.         (YEAR(CURRENT_DATE) - 3 + sequence_index) * 12 + month_index AS n
   
7.     FROM
   
8.         series_generate_integer(0, 11) AS month_index
   
9.         CROSS JOIN series_generate_integer(0, 2) AS sequence_index
   
10.     );

复制代码

1. SELECT DISTINCT
   
2.     TO_VARCHAR(ADD_MONTHS(CURRENT_DATE, months_back), 'YYYY.MM') AS YearMonth
   
3. FROM
   
4.     (
   
5.     SELECT 0 AS months_back FROM DUMMY
   
6.     UNION ALL SELECT 1 FROM DUMMY
   
7.     UNION ALL SELECT 2 FROM DUMMY
   
8.     UNION ALL SELECT 3 FROM DUMMY
   
9.     UNION ALL SELECT 4 FROM DUMMY
   
10.     UNION ALL SELECT 5 FROM DUMMY
    
11.     UNION ALL SELECT 6 FROM DUMMY
    
12.     UNION ALL SELECT 7 FROM DUMMY
    
13.     UNION ALL SELECT 8 FROM DUMMY
    
14.     UNION ALL SELECT 9 FROM DUMMY
    
15.     UNION ALL SELECT 10 FROM DUMMY
    
16.     UNION ALL SELECT 11 FROM DUMMY
    
17.     ) AS months_table
    
18. CROSS JOIN DUMMY;

复制代码

Oracle：

1. SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), - (LEVEL - 1)), 'YYYY.MM') AS YearMonth
   
2. FROM DUAL
   
3. CONNECT BY LEVEL <= 36
   
4. ORDER BY YearMonth DESC;

复制代码

Mysql：

1. SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL n MONTH), '%Y.%m') AS YearMonth
   
2. FROM (
   
3.     SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
   
4.     SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
   
5.     SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
   
6.     SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL
   
7.     SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL
   
8.     SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL
   
9.     SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL
   
10.     SELECT 35
    
11. ) AS numbers
    
12. WHERE DATE_FORMAT(DATE_SUB(NOW(), INTERVAL n MONTH), '%Y-%m-01') >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 36 MONTH), '%Y-%m-01')
    
13. ORDER BY YearMonth DESC;

复制代码

朝花夕拾 发表于 2024-7-19 09:40
Oracle：

我是HANA, 这两个我试了都会报错~