未测试,试一下吧 SELECT DateTime,F1_0001,F2_0001,Number FROM( select DateTime,F1_0001,F2_0001,Number, LAG(F1-001,1,F1_001-1)over(order by number) F3, LAG(F2-001,1,F2_001-1)over(order by number) F4 FROM TB ) T WHERE F1_001<>F3 OR F2_001<>F4 order by number desc
消息 195,级别 15,状态 10,第 4 行 'LAG' 不是可以识别的 内置函数名称。 版本:SqlServer 2008R2 Microsoft SQL Server Management Studio 10.50.1617.0 Microsoft Analysis Services 客户端工具 10.50.1617.0 Microsoft 数据访问组件 (MDAC) 6.1.7601.17514 Microsoft MSXML 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 9.11.9600.17501 Microsoft .NET Framework 2.0.50727.5485 操作系统 6.1.7601
SELECT t2.* From Tb t2 LEFT JOIN Tb t1 ON t2.Number = t1.Number + 1 WHERE t1.F1_0001 <> t2.F1_0001 OR t1.F2_0001 <> t2.F2_0001 OR t1.F1_0001 IS NULL OR t1.F2_0001 IS NULL DateTime F1_0001 F2_0001 Number -------- ------- ------- ------ 20141212 130 0 10 20141204 130 2500 4 20141203 140 2500 3 20141201 130 2500 1
OR t1.F1_0001 IS NULL OR t1.F2_0001 IS NULL 这个思路真的很赞!~
SELECT DateTime,F1_0001,F2_0001,Number
FROM(
select DateTime,F1_0001,F2_0001,Number,
LAG(F1-001,1,F1_001-1)over(order by number) F3,
LAG(F2-001,1,F2_001-1)over(order by number) F4
FROM TB
) T
WHERE F1_001<>F3 OR F2_001<>F4
order by number desc
'LAG' 不是可以识别的 内置函数名称。
版本:SqlServer 2008R2
Microsoft SQL Server Management Studio 10.50.1617.0
Microsoft Analysis Services 客户端工具 10.50.1617.0
Microsoft 数据访问组件 (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 9.11.9600.17501
Microsoft .NET Framework 2.0.50727.5485
操作系统 6.1.7601
From Tb t2
LEFT JOIN Tb t1
ON t2.Number = t1.Number + 1
WHERE t1.F1_0001 <> t2.F1_0001
OR t1.F2_0001 <> t2.F2_0001
OR t1.F1_0001 IS NULL
OR t1.F2_0001 IS NULL
DateTime F1_0001 F2_0001 Number
-------- ------- ------- ------
20141212 130 0 10
20141204 130 2500 4
20141203 140 2500 3
20141201 130 2500 1
OR t1.F1_0001 IS NULL
OR t1.F2_0001 IS NULL
这个思路真的很赞!~