永洪社区

标题: 如何在数据集中直接生成一组数 [打印本页]
作者: yhdata_x6S3fRoO    时间: 2024-7-19 09:29
标题: 如何在数据集中直接生成一组数
比如我想要一个近三年的年月数据 如2024.07
2024.06
2024.06
...


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

作者: 朝花夕拾    时间: 2024-7-19 09:29
试试这两个，我用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;
复制代码
作者: 朝花夕拾    时间: 2024-7-19 09:40
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;
复制代码

作者: yhdata_x6S3fRoO    时间: 2024-7-19 11:50
朝花夕拾 发表于 2024-7-19 09:40
Oracle：

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



欢迎光临 永洪社区 (https://club.yonghongtech.com/) Powered by Discuz! X3.4