表TEST四个字段ID,A,B,C如下显示数据
ID A B C
1 A B C
2 A B C
3 D E F
4 D E F
5 G H I
6 J K L
怎么样一条SQL显示出A,B,C这三列重复数据
ID A B C
1 A B C
2 A B C
3 D E F
4 D E F
ID A B C
1 A B C
2 A B C
3 D E F
4 D E F
5 G H I
6 J K L
怎么样一条SQL显示出A,B,C这三列重复数据
ID A B C
1 A B C
2 A B C
3 D E F
4 D E F
where (select count(1) from test where a=t.a and b=t.b and c=t.c)>1
go
create table [test]([ID] int,[A] varchar(1),[B] varchar(1),[C] varchar(1))
insert [test]
select 1,'A','B','C' union all
select 2,'A','B','C' union all
select 3,'D','E','F' union all
select 4,'D','E','F' union all
select 5,'G','H','I' union all
select 6,'J','K','L'
goselect * from test t
where (select count(1) from test where a=t.a and b=t.b and c=t.c)>1
/**
ID A B C
----------- ---- ---- ----
1 A B C
2 A B C
3 D E F
4 D E F(4 行受影响)
**/
if OBJECT_ID('tb','U') is not null drop table tb
go
create table tb
(
id int,
col1 varchar(5),
col2 varchar(5),
col3 varchar(5)
)
go
insert into tb
select '1','A','B','C' union all
select '2','A','B','C' union all
select '3','D','E','F' union all
select '4','D','E','F' union all
select '5','G','H','I' union all
select '6','J','K','L'
go
with cte as
(
select
ID,
col1,
col2,
col3,
COUNT(1) over(partition by col1,col2,col3) as v_count
from tb A
)
select
id,
col1,
col2,
col3
from cte
where v_count>1--ID Col1 col2 Col3
--1 A B C
--2 A B C
--3 D E F
--4 D E F
where (select count(1) from test where A=t.A and B=t.B and C=t.C)>1
SELECT * FROM TEST AS A
WHERE EXISTS(SELECT 1 FROM TEST AS X WHERE X.A=A.A AND X.B=A.B AND X.C=A.C AND X.ID<>A.ID)性能上這個比較好