数据库临时表-公共表表达式(CTE)

数据库临时表(CTE)

1.数据库临时表-递归公共表表达式（CTE）来生成数字序列

WITH RECURSIVE `nums` AS (SELECT 1 AS `num`
                            UNION ALL
                            SELECT `num` + 1
                            FROM `nums`
                            WHERE `num` < 10)
SELECT * from `nums` 

2.数据库临时表-递归公共表表达式（CTE）来生成时间序列

WITH RECURSIVE `dates` AS (SELECT DATE_FORMAT('2024-10-01', '%Y-%m-01') AS `date`
              UNION ALL
              SELECT DATE_ADD(`date`, INTERVAL 1 DAY)
              FROM `dates`
              WHERE DATE_ADD(`date`, INTERVAL 1 DAY) <
                     DATE_ADD(LAST_DAY('2024-10-01'), INTERVAL 1 DAY))
SELECT * FROM `dates`

注意: 以上递归公共表达式仅限 mysql8 版本的数据库支持, 如果是其他数据库版本, 可以查找支持的表达式!!!

3.或者直接用代码来生成公共表

String[] dateArray = new String[]{"2024-10-01", "2024-10-02"..., "2024-10-31"};
String sqlStr = IntStream.range(0, dateArray.length)
       .mapToObj(i -> i == 0
              ? "SELECT '" + dateArray[i] + "' AS `date`"
              : "SELECT '" + dateArray[i] + "'")
       .collect(Collectors.joining(" UNION ALL "));
System.out.println("sqlStr=" + sqlStr);

注意: 这种写法虽然不受数据库及版本限制, 但是对拼接长度有要求, 建议仅在可以预见的数据范围内拼接, 比如周,月等...