SQL生成一年每一天的时间列表的几种方法
时间：2021-07-01 10:21:17
帮助过：86人阅读
/*2012 开始已支持OFFSET 语法，不再推荐使用TOP N */
s1.i + s2.i
+ s3.i
+ s4.i
+ s5.i
+ s6.i
+ s7.i
+ s8.i
+ s9.i
AS seq
FROM (
SELECT i
= 0 UNION ALL SELECT i
= 1 ) s1
CROSS JOIN (
SELECT 0 as i
UNION ALL SELECT 2 as i) s2
CROSS JOIN (
SELECT 0 as i
UNION ALL SELECT 4 as i) s3
CROSS JOIN (
SELECT 0 as i
UNION ALL SELECT 8 as i) s4
CROSS JOIN (
SELECT 0 as i
UNION ALL SELECT 16 as i) s5
CROSS JOIN (
SELECT 0 as i
UNION ALL SELECT 32 as i) s6
CROSS JOIN (
SELECT 0 as i
UNION ALL SELECT 64 as i ) s7
CROSS JOIN (
SELECT 0 as i
UNION ALL SELECT 128 as i ) s8
CROSS JOIN (
SELECT 0 as i
UNION ALL SELECT 256 as i ) s9
ORDER BY seq
OFFSET 0 ROWS
FETCH NEXT 366 ROWS
ONLY
/* 1-2：如果系统表列数不够或者过多，会对结果或性能有影响 */
SELECT ROW_NUMBER() OVER (ORDER BY a1.object_id) - 1 AS seq
FROM sys.all_columns a1 WITH ( NOLOCK ) ,
sys.all_columns a2 WITH ( NOLOCK )
ORDER BY seq
OFFSET 0 ROWS FETCH NEXT 366 ROWS ONLY
/* 1-3：循环，不推荐的写法 */
DECLARE @I INT
DECLARE @seq TABLE
(
seq INT NOT NULL
)
SET @I=0
WHILE @I < 366
BEGIN
INSERT INTO @seq(seq)
VALUES(@I)
SET @I+=1
END
SELECT * FROM @seq
2、基于以上的任意一种序列，使用DATEADD(DAY,seq,@DstDate) 即可生成一年中的每一天的时间列表。
3、拓展：MySQL中如何利用以上的思路，在不编写存储过程或函数的前提下，利用SQL 语句拆分一个字符串到数组？
点此传送查看答案>>>
SQL生成一年每一天的时间列表的几种方法
标签：思路 href end www object_id 时间 数组 拓展 fse
