选出不是连续重复的..

it_sql 2009-11-05 09:27:00

在一个EXCEL的SHEET1中
A列
aa
aa
aa
aa
aa
bb
bb
bb
bb
cc
cc
cc
cc
cc
cc
cc
cc
aa
cccc
cccc
cccc
cccc
cccc
bb
bb
bb
bb
ccc
ccc
ccc
ccc
....

怎么找出不是连续的,有10000~30000行比如上面列子中的,aa和bb

...全文

88 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

it_sql 2009-11-05

打赏
举报

能不能解析一下怎么做到的?谢谢

csdyyr 2009-11-05

打赏
举报

DECLARE @TB TABLE([COL] VARCHAR(4))

INSERT @TB 

SELECT 'aa' UNION ALL 

SELECT 'aa' UNION ALL 

SELECT 'aa' UNION ALL 

SELECT 'aa' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'aa' UNION ALL 

SELECT 'cccc' UNION ALL 

SELECT 'cccc' UNION ALL 

SELECT 'cccc' UNION ALL 

SELECT 'cccc' UNION ALL 

SELECT 'cccc' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'ccc' UNION ALL 

SELECT 'ccc' UNION ALL 

SELECT 'ccc' UNION ALL 

SELECT 'ccc'



SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM @TB 



SELECT COL

FROM (SELECT *, ID-(SELECT COUNT(*) FROM #  WHERE COL=T.COL AND ID<=T.ID) AS DIF FROM # AS T) AS T2

GROUP BY COL

HAVING COUNT(DISTINCT DIF)>1



DROP TABLE #

/*



COL  

---- 

aa

bb

*/

it_sql 2009-11-05

打赏
举报

是啊,是很乱啊....

acen_chen 2009-11-05

打赏
举报

又不按字符串排序，怎么判定连续不连续。。。
cccc在ccc上面。。。
太乱。。。

xuejie09242 2009-11-05

打赏
举报

不太明白意思。。。

--小F-- 2009-11-05

打赏
举报

...想简单了

you_tube 2009-11-05

打赏
举报

不明白，帮顶

--小F-- 2009-11-05

打赏
举报

在SQL中

select col from tb group by col having count(1)=1

renadg 2009-11-05

打赏
举报

强!学习了!

csdyyr 2009-11-05

打赏
举报

[Quote=引用 8 楼 it_sql 的回复:]
能不能解析一下怎么做到的?谢谢
[/Quote]



--如果 是連續的ID,那麼減去連續的編號SEQ,那麼每一行的差應該相同.

--例如對於aa, 最後一條ID不連續,所以減去連續的SEQ後所得的差跟前面的紀錄不同了

DECLARE @TB TABLE([COL] VARCHAR(4))

INSERT @TB 

SELECT 'aa' UNION ALL 

SELECT 'aa' UNION ALL 

SELECT 'aa' UNION ALL 

SELECT 'aa' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'cc' UNION ALL 

SELECT 'aa' UNION ALL 

SELECT 'cccc' UNION ALL 

SELECT 'cccc' UNION ALL 

SELECT 'cccc' UNION ALL 

SELECT 'cccc' UNION ALL 

SELECT 'cccc' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'bb' UNION ALL 

SELECT 'ccc' UNION ALL 

SELECT 'ccc' UNION ALL 

SELECT 'ccc' UNION ALL 

SELECT 'ccc'



SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM @TB 



SELECT *,ID-SEQ AS DIF

FROM (

SELECT *, (SELECT COUNT(*) FROM #  WHERE COL=T.COL AND ID<=T.ID) AS SEQ FROM # AS T

WHERE COL='aa'

) AS T2



DROP TABLE #

/*

COL  ID          SEQ         DIF         

---- ----------- ----------- ----------- 

aa   1           1           0

aa   2           2           0

aa   3           3           0

aa   4           4           0

aa   17          5           12

*/