求SQL,最后一条记录和上次出现记录之间间隔了多少条记录.

Question

DECLARE @t Table (
ID INT NOT NULL, 
IP varchar(200) NOT NULL 
)INSERT @t 
SELECT 1 AS [ID], 'a' AS [IP] 
UNION ALL 
SELECT 2 AS [ID], 'c' AS [IP] 
UNION ALL 
SELECT 3 AS [ID], 'b' AS [IP] 
UNION ALL 
SELECT 5 AS [ID], 'd' AS [IP] 
UNION ALL 
SELECT 6 AS [ID], 'a' AS [IP] 
UNION ALL 
SELECT 8 AS [ID], 'c' AS [IP] 
UNION ALL 
SELECT 9 AS [ID], 'b' AS [IP] SELECT COUNT(t.ID) 
FROM @t t ,  (SELECT L.ID AS LastID, ISNULL(S.ID,0) AS SecondID 
FROM (SELECT TOP 1 ID, IP FROM @t t WHERE IP='a' ORDER BY ID DESC) L 
OUTER APPLY (SELECT TOP 1 ID FROM @t t WHERE IP='a' AND ID<>L.ID ORDER BY ID DESC)  S 
) O 
WHERE t.ID >=O.SecondID AND t.ID<O.LastID 

Answer 1

谢谢LS，看起来好复杂的样子，几乎看不懂。我的是MSSQL2008，有没有更简单的写法。

Answer 2

既然2008，你用row_number在查找的时候生成一个连续的序列，就可以获取顺序的

Answer 3

DECLARE @t Table (
ID INT NOT NULL, 
IP varchar(200) NOT NULL 
)
 
INSERT @t 
SELECT 1 AS [ID], 'a' AS [IP] 
UNION ALL 
SELECT 2 AS [ID], 'c' AS [IP] 
UNION ALL 
SELECT 3 AS [ID], 'b' AS [IP] 
UNION ALL 
SELECT 5 AS [ID], 'd' AS [IP] 
UNION ALL 
SELECT 6 AS [ID], 'a' AS [IP] 
UNION ALL 
SELECT 8 AS [ID], 'c' AS [IP] 
UNION ALL 
SELECT 9 AS [ID], 'b' AS [IP] 
UNION ALL 
SELECT 15 AS [ID], 'b' AS [IP] 
UNION ALL 
SELECT 18 AS [ID], 'd' AS [IP]  
select r.IP,r2.Counts 
from (
 select IP,MAX(ID) as MaxId,MIN(id) MinId from @t 
 group by IP ) r
 Outer Apply (
select COUNT(1) as Counts from @t where ID between r.MinId and r.MaxId -1 
 ) r2
 比1楼简单点

Answer 4

已经用SQL2005的ROW_NUMBER() OVER(ORDER BY COL DESC)的方式解决了。