|

楼主 |
发表于 2019-7-18 14:33:17
|
显示全部楼层
引用1楼HelloWorld,的回复:- DECLARE@tTABLE(DDATENOTNULL,TINTNOTNULL,IDVARCHAR(10)NOTNULL);
- INSERT@t(D,T,ID)
- VALUES('2019/6/30',104739,'A00001'),('2019/6/30',104740,'A00001'),('2019/6/30',104744,'A00001'),
- ('2019/6/30',104745,'A00001'),('2019/6/30',190727,'A00002'),('2019/6/30',213819,'A00003'),
- ('2019/6/30',213830,'A00004'),('2019/7/1',215106,'A00007'),('2019/7/1',215109,'A00009'),
- ('2019/7/1',215109,'A00005'),('2019/7/1',215109,'A00010');
-
- WITHDAS(SELECT*,T/10000*3600+T/100*60+T%60SFROM@t),
- D1AS(SELECT*,
- LEAD(ID,2)OVER(ORDERBYD.ID,D.D,D.T)LeadID,
- LEAD(S,2)OVER(ORDERBYD.ID,D.D,D.T)LeadS,
- LAG(ID,2)OVER(ORDERBYD.ID,D.D,D.T)LagID,
- LAG(S,2)OVER(ORDERBYD.ID,D.D,D.T)LagS
- FROMD)
- SELECTD1.D,D1.T,D1.ID
- FROMD1
- WHERE(D1.LeadID=D1.IDANDD1.LeadS-D1.S<=300)
- OR(D1.LagID=D1.IDANDD1.S-D1.LagS<=300);
复制代码
(11行受影响)
DTID
-------------------------------
2019-06-30104739A00001
2019-06-30104740A00001
2019-06-30104744A00001
2019-06-30104745A00001
(4行受影响)
谢谢大神,已经成功了,感动
|
|