ID Type1 Type2 Created
---------------------------------------------
1 A B 2014-01-25 00:34:32.630
1 A B 2014-01-23 00:34:31.630
1 C K 2014-01-22 00:34:30.630
1 Z Y 2014-01-21 00:34:29.630
1 H L 2014-01-20 00:34:28.630
1 H L 2014-01-19 00:34:27.630
1 A B 2014-01-18 00:34:26.630
如上。如果ID，type1并且type2是相同的，给我的最低创建日期，但只有当它们彼此相邻并没有间隙。
这就是它的样子
ID Type1 Type2 Created
---------------------------------------------
1 A B 2014-01-23 00:34:31.630
1 C K 2014-01-22 00:34:30.630
1 Z Y 2014-01-21 00:34:29.630
1 H L 2014-01-19 00:34:27.630
1 A B 2014-01-18 00:34:26.630
祝你好运，谢谢你
试试这个：
测试数据：
DECLARE @t table
(ID int, Type1 char(1), Type2 char(1), Created datetime)
INSERT @t values
(1,'A','B','2014-01-25 00:34:32.630'),
(1,'A','B','2014-01-23 00:34:31.630'),
(1,'C','K','2014-01-22 00:34:30.630'),
(1,'Z','Y','2014-01-21 00:34:29.630'),
(1,'H','L','2014-01-20 00:34:28.630'),
(1,'H','L','2014-01-19 00:34:27.630'),
(1,'A','B','2014-01-18 00:34:26.630')
询问：
;WITH CTE AS
(
SELECT
ID, Type1, Type2, Created,
dense_rank() over (order by Created) rn1,
rank() over (partition by id, type1, type2 order by Created) rn2
FROM @t
)
SELECT ID, Type1, Type2, min(Created) Created
FROM cte
GROUP BY ID, Type1, Type2, rn1 - rn2
ORDER BY Created
结果：
ID Type1 Type2 Created
1 A B 2014-01-18 00:34:26.630
1 H L 2014-01-19 00:34:27.630
1 Z Y 2014-01-21 00:34:29.630
1 C K 2014-01-22 00:34:30.630
1 A B 2014-01-23 00:34:31.630
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句