价值 100 RMB 的一道 SQL 题

dbLenis
博客专家认证
2018-11-29 10:54:29
很久之前,在 SQL QQ 群讨论的时候,

有个哥们扔出一道题,赏金 100 RMB。

一礼拜后,才有人解出来!



大家来看看这是一道什么题?

仔细看图片,看懂也是一种能力!!!







最后夺金的勇者是一位资深开发,之所以说资深,他已经工作很久了,久到忘记自己是80,还是70后了

答案我已经提示在我的博客空间,有兴趣自取之!
...全文
620 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
快乐起航2020 2018-12-21
  • 打赏
  • 举报
回复
按第三个字段排序而已,难?
宇宁 2018-12-14
  • 打赏
  • 举报
回复
mark 研究下
weixin_42334999 2018-12-11
  • 打赏
  • 举报
回复
现在的100块这么不值钱了吗。。。
RINK_1 2018-12-10
  • 打赏
  • 举报
回复


WITH CTE_1
AS 
(SELECT ROW_NUMBER() OVER (ORDER BY GETDATE())-1 AS SEQ
 FROM MASTER.DBO.SPT_VALUES A
 JOIN MASTER.DBO.SPT_VALUES B ON 1=1
 WHERE A.TYPE='P' AND B.TYPE='P' AND A.NUMBER<=1000 AND B.NUMBER<=100 ),

CTE_2
AS
(SELECT *,DATEADD(DAY,B.SEQ,A.EFFECTIVE_DATE) AS NEW_DATE
FROM TABLE A
JOIN CTE_1 B ON B.SEQ<=DATEDIFF(DAY,EFFECTIVE_DATE,CASE WHEN EXPIRATION_DATE='9999-12-31' THEN '2099-12-31' ELSE EXPIRATION_DATE END)
),

CTE_3
AS
(SELECT NEW_DATE,MAX(NUMBER) AS NUMBER FROM CTE_2
GROUP BY NEW_DATE)

SELECT NUMBER,MIN(NEW_DATE) AS START_DATE,
CASE WHEN MAX(NEW_DATE)='2099-12-31' THEN '9999-12-31' ELSE MAX(NEW_DATE) END AS END_DATE
FROM CTE_3
GROUP BY NUMBER
ORDER BY MIN(NEW_DATE)

疯狂的疯 2018-12-09
  • 打赏
  • 举报
回复
刚才忘了上结果图。
疯狂的疯 2018-12-09
  • 打赏
  • 举报
回复
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
	DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[ID] INT
,[NUMBER] INT
,[EFFECTIVE_DATE] DATE
,[EXPIRATION_DATE] DATE
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(1,50,'2018-01-01','2018-04-01')
INSERT INTO dbo.[t] VALUES(1,70,'2018-04-02','2018-06-06')
INSERT INTO dbo.[t] VALUES(1,60,'2018-06-07','9999-12-31')
INSERT INTO dbo.[t] VALUES(1,100,'2018-02-01','2018-04-15')
INSERT INTO dbo.[t] VALUES(1,200,'2018-05-15','2018-07-01')
select * from t order by EFFECTIVE_DATE
;WITH cte AS (
	SELECT 
	ROW_NUMBER() OVER (ORDER BY [EFFECTIVE_DATE]) AS rid
	,* 
	FROM t
)
SELECT 
a.ID
,a.[NUMBER]
,CASE WHEN a.[NUMBER]<ISNULL(b.[NUMBER],0) and a.[EFFECTIVE_DATE]<b.[EXPIRATION_DATE] THEN DATEADD(DAY,1,b.[EXPIRATION_DATE]) ELSE a.[EFFECTIVE_DATE] END AS [EFFECTIVE_DATE]
,CASE WHEN a.[NUMBER]>c.[NUMBER] and  a.[EXPIRATION_DATE]>c.[EFFECTIVE_DATE] THEN a.[EXPIRATION_DATE]  ELSE Isnull(DATEADD(DAY,-1,c.[EFFECTIVE_DATE]),a.[EXPIRATION_DATE]) END AS [EXPIRATION_DATE]
FROM cte AS a
LEFT JOIN cte AS b ON a.rid=b.rid+1
LEFT JOIN cte AS c ON a.rid=c.rid-1
ORDER BY a.rid


 

看到答案不一定就是对的,借用2楼代码一用。
  • 打赏
  • 举报
回复
类似接龙咯~
guguda2008 2018-12-07
  • 打赏
  • 举报
回复
根据交叉的时间区间表生成不交叉的时间区间表嘛,你这个还没加入多ID的情况。

这个查询的关键在于找到每条记录所有同ID的与之交叉起点里最早的时间点,然后生成其前一天记录作为新的区间终点,然后再找到所有与之交叉的终点里的最晚的的时间点,然后生成其后一天作为新的区间终点,然后在新生成的表里查询所有时间区间与原表开始时间或结束时间相同,且时间区间最短的区间即可。

先想清楚算法,再写SQL实现就好了。
dbLenis 2018-12-02
  • 打赏
  • 举报
回复
引用 5 楼 xiafan 的回复:
但这个结果和原图中第五行数据不符啊。
原图中第5行是7月1日啊。除非你这第四行中的是6月30日。


其实是我图中的错误。理应是 7月2日
xiafan 2018-12-01
  • 打赏
  • 举报
回复
但这个结果和原图中第五行数据不符啊。 原图中第5行是7月1日啊。除非你这第四行中的是6月30日。
卖水果的net 版主 2018-11-30
  • 打赏
  • 举报
回复
mark..
吉普赛的歌 版主 2018-11-30
  • 打赏
  • 举报
回复
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL 
	DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[ID] INT
,[NUMBER] INT
,[EFFECTIVE_DATE] DATE
,[EXPIRATION_DATE] DATE
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(1,50,'2018-01-01','2018-04-01')
INSERT INTO dbo.[t] VALUES(1,70,'2018-04-02','2018-06-06')
INSERT INTO dbo.[t] VALUES(1,60,'2018-06-07','9999-12-31')
INSERT INTO dbo.[t] VALUES(1,100,'2018-02-01','2018-04-15')
INSERT INTO dbo.[t] VALUES(1,200,'2018-05-15','2018-07-01')

;WITH cte AS (
	SELECT 
	ROW_NUMBER() OVER (ORDER BY [EFFECTIVE_DATE]) AS rid
	,* 
	FROM t
)
SELECT 
a.ID
,a.[NUMBER]
,CASE WHEN a.[EFFECTIVE_DATE]<b.[EXPIRATION_DATE] THEN DATEADD(DAY,1,b.[EXPIRATION_DATE]) ELSE a.[EFFECTIVE_DATE] END AS [EFFECTIVE_DATE]
,CASE WHEN a.[EFFECTIVE_DATE]>c.[EXPIRATION_DATE] THEN DATEADD(DAY,1,c.[EXPIRATION_DATE]) ELSE a.[EXPIRATION_DATE] END AS [EXPIRATION_DATE]
FROM cte AS a
LEFT JOIN cte AS b ON a.rid=b.rid+1
LEFT JOIN cte AS c ON a.rid=c.rid-1
ORDER BY a.rid
dbLenis 2018-11-30
  • 打赏
  • 举报
回复
引用 2 楼 吉普赛的歌 的回复:
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[ID] INT
,[NUMBER] INT
,[EFFECTIVE_DATE] DATE
,[EXPIRATION_DATE] DATE
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(1,50,'2018-01-01','2018-04-01')
INSERT INTO dbo.[t] VALUES(1,70,'2018-04-02','2018-06-06')
INSERT INTO dbo.[t] VALUES(1,60,'2018-06-07','9999-12-31')
INSERT INTO dbo.[t] VALUES(1,100,'2018-02-01','2018-04-15')
INSERT INTO dbo.[t] VALUES(1,200,'2018-05-15','2018-07-01')

;WITH cte AS (
SELECT
ROW_NUMBER() OVER (ORDER BY [EFFECTIVE_DATE]) AS rid
,*
FROM t
)
SELECT
a.ID
,a.[NUMBER]
,CASE WHEN a.[EFFECTIVE_DATE]<b.[EXPIRATION_DATE] THEN DATEADD(DAY,1,b.[EXPIRATION_DATE]) ELSE a.[EFFECTIVE_DATE] END AS [EFFECTIVE_DATE]
,CASE WHEN a.[EFFECTIVE_DATE]>c.[EXPIRATION_DATE] THEN DATEADD(DAY,1,c.[EXPIRATION_DATE]) ELSE a.[EXPIRATION_DATE] END AS [EXPIRATION_DATE]
FROM cte AS a
LEFT JOIN cte AS b ON a.rid=b.rid+1
LEFT JOIN cte AS c ON a.rid=c.rid-1
ORDER BY a.rid




这位牛 GG, 咱能留点悬念给读者不?
好让大家伙都动动脑筋,您说对不?

二月十六 版主 2018-11-30
  • 打赏
  • 举报
回复
感谢分享

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧