题目
有如下一张表T0311
希望得到如下结果:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-A5EUtvOe-1615905910073)(./images/结果-每日一题20210315.png)]
即将连续的日期列分成两列显示
测试数据
CREATE TABLE T0316 (DATE1 DATETIME)
GO
DECLARE @DATE1 DATETIME
SET @DATE1='2020-1-25'
WHILE @DATE1<GETDATE()
BEGIN
INSERT INTO T0316 VALUES(@DATE1)
SELECT @DATE1=DATEADD(M,1,@DATE1)
END
GO
参考答案
官方答案:
;
WITH T AS (
SELECT ROW_NUMBER() over (ORDER BY DATE1) ID, DATE1
FROM T0316
)
-- 关联
SELECT T1.DATE1 AS DATE1, T2.DATE1 AS DATE2
FROM (SELECT ROW_NUMBER() over (ORDER BY DATE1) ID, DATE1 FROM T WHERE ID % 2 = 1) T1
JOIN
(SELECT ROW_NUMBER() over (ORDER BY DATE1) ID, DATE1 FROM T WHERE ID % 2 = 0) T2
ON T1.ID = T2.ID
考点: ROW_NUMBER() over (ORDER BY 列)
分析:
- 将时间排序编号
- 将奇数、偶数编号进行过滤重新编号
- 关联奇数、偶数
我当时测试做的答案
SELECT T1.DATE1 AS DATE1,T2.DATE1 AS DATE2 FROM (SELECT * FROM T0316
WHERE DATEPART(MONTH,DATE1)%2=1) T1,T0316 T2
WHERE DATEPART(MONTH,T2.DATE1)%2=0
AND DATEDIFF(MONTH, T1.DATE1,T2.DATE1)=1
ORDER BY DATE1
我这样做也只是根据创建脚本来做,不严谨