求连续出现三次的某一数值的算法

DECLARE @T TABLE (ID INT,NAME VARCHAR(4),STATUS INT)
INSERT INTO @T
SELECT 1,'aa',0 UNION ALL
SELECT 2,'aa',1 UNION ALL
SELECT 3,'aa',0 UNION ALL
SELECT 4,'bb',1 UNION ALL
SELECT 7,'bb',1 UNION ALL
SELECT 9,'bb',1 UNION ALL
SELECT 10,'aa',0 UNION ALL
SELECT 11,'cc',0
--

--常规语句法，此方法不要求主键连续，但假如求连续一百次的就脸长了

select a.name from @t a,
@t b,@t c
where a.name=b.name
and b.name=c.name
and b.id=(select top 1 id from @t where id>a.id order by id)
and c.id=(select top 1 id from @t where id>b.id order by id);

--CTE递归法，要求主键必须连续，但很适用于求连续次数较多的场合
WITH testCTE(id,name,lvl)
AS
(
-- Anchor Member (AM)
select id,name,0
from @t
UNION ALL
SELECT e.id,e.name,m.lvl+1
FROM @t AS E,testCTE AS M
where M.name=E.name
and M.id=E.id+1)

SELECT * FROM testCTE
where lvl=2

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12172/viewspace-1018374/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12172/viewspace-1018374/