請教SQL

[gopark]

數據如下
NO C1 C2 Date
A X1 00 2006/01/01
A X1 01 2006/01/03
A X1 02 2006/01/04
A X2 02 2006/01/04
A X1 01 2006/01/07
A X1 00 2006/01/08
A X1 02 2006/01/09
A X1 00 2006/01/10
要求是抓出同一個C1編號在C2連續出現'00','01','02'的數據,00,01,02的出現先後次序無關,結果是
A X1 00 2006/01/01
A X1 01 2006/01/03
A X1 02 2006/01/04
A X1 01 2006/01/07
A X1 00 2006/01/08
A X1 02 2006/01/09

[splory]

暂时还没有思路，帮你顶一下

[WangZWang]

Declare @tb table(No varchar(8),C1 varchar(8),C2 varchar(2),date varchar(20))
Insert Into @tb
Select 'A','X1','00','2006/01/01'
union all Select 'A','X1','01','2006/01/03'
union all Select 'A','X1','02','2006/01/04'
union all Select 'A','X2','02','2006/01/04'
union all Select 'A','X1','01','2006/01/07'
union all Select 'A','X1','00','2006/01/08'
union all Select 'A','X1','02','2006/01/09'
union all Select 'A','X1','00','2006/01/10'
select * from @tb

Select * from @tb as a
where exists(Select * from(
Select C1,C2 from @tb
Union
Select C1,-1 from @tb) as b
where C1=a.C1 and C2=a.C2-1)

[fcuandy]

楼上朋友的语句似乎有点问题.
只是结果数据恰好正确.

而楼主的逻辑,第5,6,7,8条记录C1相同,C2都是00,01,02之内,加上不论顺序,所以无法区别取哪条.当然,如果有条件限制:取前三条,或后三条,就可以确定.所以在不加条件的情况下,最后一条记录是取出来的.

Declare @tb table(No varchar(8),C1 varchar(8),C2 varchar(2),date varchar(20))
Insert Into @tb
Select 'A','X1','00','2006/01/01'
union all Select 'A','X1','01','2006/01/03'
union all Select 'A','X1','02','2006/01/04'
union all Select 'A','X2','02','2006/01/04'
union all Select 'A','X1','01','2006/01/07'
union all Select 'A','X1','00','2006/01/08'
union all Select 'A','X1','02','2006/01/09'
union all Select 'A','X1','00','2006/01/10'
select *,IDENTITY(int) ID INTO #t from @tb

SELECT a.*,NULLIF(a.c1,b.c1) g
INTO #t1
FROM #t a
LEFT JOIN #t b
ON a.id=b.id+1
SELECT a.*,GID=(
CASE ISNULL(a.g,'') WHEN '' THEN
(SELECT MAX(ID) FROM #t1 b WHERE a.C1=b.C1 AND b.ID<=a.ID AND b.g IS NOT NULL)
ELSE
a.id
END
)
INTO #base
FROM #t1 a

SELECT * FROM #base a
WHERE
CHARINDEX(C2,'00,01,02')>0
AND
NOT EXISTS
(
SELECT 1 FROM #base b WHERE a.GID=b.GID AND CHARINDEX(b.C2,'00,01,02')<1
)
AND NOT EXISTS
(
SELECT 1 FROM (SELECT '00' C2 UNION SELECT '01' UNION SELECT '02') z
WHERE z.C2 NOT IN(SELECT c2 FROM #base w WHERE w.Gid=a.Gid)
)

DROP TABLE #t
DROP TABLE #t1
DROP TABLE #base

/*结果.
A X1 00 2006/01/01 1 X1 1
A X1 01 2006/01/03 2 NULL 1
A X1 02 2006/01/04 3 NULL 1
A X1 01 2006/01/07 5 X1 5
A X1 00 2006/01/08 6 NULL 5
A X1 02 2006/01/09 7 NULL 5
A X1 00 2006/01/10 8 NULL 5
*/

语句还是有可以简化的地方,为了楼主可能提出只取三条的要求,所以先这样写了,方便扩展

[fcuandy]

2楼的朋友你可以加多几条其它的测试数据试一下