在一个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
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
select col from tb group by col having count(1)=1
cccc在ccc上面
太乱
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)>1DROP TABLE #
/*COL
----
aa
bb
*/
--如果 是連續的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 T2DROP 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
*/