新入职了一家公司,246加班,实在是扛不住了,遂跑路,入职这一个多月,不仅时间不长,而且做的也不少(/狗头),而且还难,这边记录一个全网很难搜到,ai也总是胡说八道的问题
在mysql8中生成日期序列:
WITH RECURSIVE DateRange AS
( SELECT '2024-06-08' AS date
UNION ALL
SELECT DATE_ADD( date, INTERVAL 1 DAY ) FROM DateRange WHERE date < '2024-06-15' )
SELECT dr.date AS time
FROM
DateRange dr
GROUP BY
dr.date
ORDER BY
dr.date
查询结果如图,查询出了从8号到15号的日期数据:
首先oracle中是不支持WITH RECURSIVE这种递归写法的,此处要改,其次oracle中对于日期也要使用TO_DATE()函数来处理
让我们改写一下,此处可以询问ai(如通义千问),由于是递归语句,ai会帮忙修改成oracle中的递归语句:
WITH DateRange ("time_range") AS (
SELECT TO_DATE('2024-06-08', 'YYYY-MM-DD') FROM DUAL
UNION ALL
SELECT "time_range" + INTERVAL '1' DAY
FROM DateRange
WHERE "time_range" + INTERVAL '1' DAY < TO_DATE('2024-06-15', 'YYYY-MM-DD')
)
SELECT
dr."time_range" AS time
FROM
DateRange dr
GROUP BY
dr."time_range"
ORDER BY
dr."time_range";
sql也非常简单,语义上和mysql中也完全一致,然而这句sql在oracle 11g中是无法执行的:
这句话tmd是什么意思呢,这句话你不论去百度还是问ai都是很难理解它是什么意思的,oracle的提示就是这么的不明觉吊,这句话其实是说你union all两侧的数据类型不一致,也就是这段代码:
WITH DateRange ("time_range") AS (
SELECT TO_DATE('2024-06-08', 'YYYY-MM-DD') FROM DUAL -- 日期
UNION ALL
SELECT "time_range" + INTERVAL '1' DAY -- 按理说也是日期,但是实际上不是
FROM DateRange
WHERE "time_range" + INTERVAL '1' DAY < TO_DATE('2024-06-15', 'YYYY-MM-DD')
)
这就非常蛋疼了,用日期+1天生成出来的数据不是日期?此处卡了我起码一个下午,在第N次询问ai时,ai给了一个非递归的写法(事实上我是在几天之后才灵光一现回想起来ai给了个非递归的写法):
WITH DateRange ("time_range") AS (
SELECT TO_DATE('2024-06-08', 'YYYY-MM-DD') + LEVEL - 1 FROM DUAL
CONNECT BY LEVEL <= TO_DATE('2024-06-15', 'YYYY-MM-DD') - TO_DATE('2024-06-08', 'YYYY-MM-DD') + 1
)
SELECT
dr."time_range" AS time
FROM
DateRange dr
GROUP BY
dr."time_range"
ORDER BY
dr."time_range";
专门去查了下,这个是oracle中的层次查询
这个查询之所以能够成功执行,是因为它使用了Oracle中的层次查询(Hierarchical Query)功能,特别是CONNECT BY子句,来生成一个日期序列,而不是通过递归CTE的方式。这种方式避开了之前直接对日期进行数学运算(如加1)导致的数据类型不匹配问题。
具体分析如下:
层级查询(Hierarchical Query): 使用CONNECT BY构建了一个层级查询,其中LEVEL是一个内置伪列,用于生成连续的行号。在这个场景中,LEVEL从1开始,每次递增,直到达到指定的终止条件。
日期序列生成: 查询利用TO_DATE(‘2024-06-15’, ‘YYYY-MM-DD’) - TO_DATE(‘2024-06-08’, ‘YYYY-MM-DD’) + 1来计算日期差,并作为CONNECT BY的终止条件。这意味着从起始日期2024-06-08开始,生成直到2024-06-15(包括这一天)的日期序列。
日期计算: 在SELECT子句中,TO_DATE(‘2024-06-08’, ‘YYYY-MM-DD’) + LEVEL - 1这一表达式是关键。首先,将起始日期转换为DATE类型,然后加上LEVEL-1来生成序列中的每一天。减1是因为LEVEL从1开始,而我们需要从0开始递增以匹配起始日期。
数据类型一致性: 这种方法保证了所有生成的日期都是一致的DATE类型,没有尝试将整数直接加到日期上,因此避免了数据类型不匹配的错误。
综上所述,这个查询能够成功执行,因为它有效地使用了Oracle的层次查询机制来动态生成日期序列,同时确保了所有生成值的数据类型一致性,从而避开了ORA-01790错误。
似懂非懂,反正就是可以查出来日期序列了
看上去还有点小问题,后面多了时间,这边用oracle中的TO_CHAR()函数处理一下,最终sql:
WITH DateRange ("time_range") AS (
SELECT TO_DATE('2024-06-08', 'YYYY-MM-DD') + LEVEL - 1 FROM DUAL
CONNECT BY LEVEL <= TO_DATE('2024-06-15', 'YYYY-MM-DD') - TO_DATE('2024-06-08', 'YYYY-MM-DD') + 1
)
SELECT
TO_CHAR(dr."time_range", 'YYYY-MM-DD') AS time
FROM
DateRange dr
GROUP BY
dr."time_range"
ORDER BY
dr."time_range";
查询结果: