一个显示重复数据的SQL
表TEST四个字段ID,A,B,C如下显示数据
IDABC
1ABC
2ABC
3DEF
4DEF
5GHI
6JKL
怎么样一条SQL显示出A，B，C这三列重复数据
IDABC
1ABC
2ABC
3DEF
4DEF


[解决办法]

SQL code

select * from test twhere (select count(1) from test where a=t.a and b=t.b and c=t.c)>1
[解决办法]
SQL code
if OBJECT_ID('tb','U') is not null drop table tbgocreate table tb(    id int,    col1 varchar(5),    col2 varchar(5),    col3 varchar(5))goinsert into tbselect '1','A','B','C' union allselect '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'gowith 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,     col3from ctewhere v_count>1--ID Col1 col2 Col3--1    A    B    C--2    A    B    C--3    D    E    F--4    D    E    F