select col from tb group by col having count(col)>1
if object_id('tb')is not null drop table tb go create table tb(col varchar(2)) insert tb select 'a' insert tb select 'b' insert tb select 'a' insert tb select 'c' insert tb select 'a' insert tb select 'b' insert tb select 'd' insert tb select 'e' select * from tb where col in(select col from tb group by col having count(col)>1 ) /*col ---- a b a a b*/
DECLARE @TB TABLE(COL VARCHAR(1),ID INT IDENTITY(1,1)) INSERT @TB SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'a' UNION ALL SELECT 'c' UNION ALL SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'd' UNION ALL SELECT 'e'SELECT A.COL FROM @TB AS A JOIN ( SELECT COL,COUNT(*) AS C FROM @TB GROUP BY COL) B ON A.COL=B.COL WHERE C>1 ORDER BY ID /* COL ---- a b a a b */
保留重复的? select a from tb group by a having count(a) > 1 保留不重复的 select a from tb group by a having count(a) = 1
create table tb(a varchar(1)) insert into tb values('a') insert into tb values('b') insert into tb values('a') insert into tb values('c') insert into tb values('a') insert into tb values('b') insert into tb values('d') insert into tb values('e') go--保留重复的? select * from tb where a in (select a from tb group by a having count(a) > 1 ) /* a ---- a b a a b(所影响的行数为 5 行) */--保留不重复的 select * from tb where a not in (select a from tb group by a having count(a) > 1 ) /* a ---- c d e(所影响的行数为 3 行) */drop table tb
go
create table tb(col varchar(2))
insert tb select 'a'
insert tb select 'b'
insert tb select 'a'
insert tb select 'c'
insert tb select 'a'
insert tb select 'b'
insert tb select 'd'
insert tb select 'e'
select * from tb where col in(select col from tb group by col having count(col)>1 )
/*col
----
a
b
a
a
b*/
INSERT @TB
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'a' UNION ALL
SELECT 'c' UNION ALL
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'd' UNION ALL
SELECT 'e'SELECT A.COL
FROM @TB AS A JOIN (
SELECT COL,COUNT(*) AS C FROM @TB GROUP BY COL) B
ON A.COL=B.COL
WHERE C>1
ORDER BY ID
/*
COL
----
a
b
a
a
b
*/
select a from tb group by a having count(a) > 1
保留不重复的
select a from tb group by a having count(a) = 1
insert into tb values('a')
insert into tb values('b')
insert into tb values('a')
insert into tb values('c')
insert into tb values('a')
insert into tb values('b')
insert into tb values('d')
insert into tb values('e')
go--保留重复的?
select * from tb where a in (select a from tb group by a having count(a) > 1 )
/*
a
----
a
b
a
a
b(所影响的行数为 5 行)
*/--保留不重复的
select * from tb where a not in (select a from tb group by a having count(a) > 1 )
/*
a
----
c
d
e(所影响的行数为 3 行)
*/drop table tb