-- 查出 B，C 字段相同的数据
create table CongFuData(
    A varchar(2),
    B varchar(2),
    C varchar(2),
    D varchar(2),
    E varchar(2),
    F varchar(2),
    G varchar(2),
    CONSTRAINT PK_name primary key CLUSTERED(A,B,C,D) on [primary]
)
insert into congfudata values ('a', 'b', 'c','d' ,'e1','f','g')
insert into congfudata values ('a2','b', 'c','d2','e2','f','g')
insert into congfudata values ('a3','b', 'c','d3','e3','f','g')
insert into congfudata values ('a4','b4','c','d4','e4','f','g')
insert into congfudata values ('a5','b5','c','d5','e5','f','g')
-- 求教

select * from congfudata as t
where exists(select 1 from congfudata where b=t.b and c=t.c and a<>t.a)

------解决方案--------------------
SQL code
select a.* from CongFuData a 
where exists (select 1 from 
(select b,c  from CongFuData b   group by b,c having count(1)>1) b where a.b=b.b and a.c=b.c)

------解决方案--------------------
SQL code


SELECT *
FROM   congfudata AS t
WHERE  EXISTS (SELECT 1
               FROM   congfudata
               WHERE  b = t.b
                      AND c = t.c
                      AND a <> t.a)

------解决方案--------------------
SQL code

select distinct a.* 
from congfudata a,congfudata b
where a.B=b.B and a.C=b.C and a.A<>b.A
/*
A    B    C    D    E    F    G
---- ---- ---- ---- ---- ---- ----
a    b    c    d    e1   f    g
a2   b    c    d2   e2   f    g
a3   b    c    d3   e3   f    g
*/

------解决方案--------------------
SQL code

SELECT DISTINCT cdf1.*
FROM CongFuData AS cdf1
    INNER JOIN CongFuData AS cdf2
        ON cdf2.B = cdf1.B AND cdf2.C = cdf1.C AND cdf2.A <> cdf1.A

------解决方案--------------------
因为主键定义在ABCD四个字段，考虑到字段A的值可能也相同，还应该加上字段D的判断。

SQL code

SELECT DISTINCT cdf1.*
FROM CongFuData AS cdf1
    INNER JOIN CongFuData AS cdf2
        ON (cdf2.B = cdf1.B AND cdf2.C = cdf1.C ) AND( cdf2.A <> cdf1.A OR cdf2.D <> cdf1.D)

------解决方案--------------------
select a.* from CongFuData a  
where exists (select 1 from  
(select b,c  from CongFuData b   group by b,c having count(1)>1) b where a.b=b.b and a.c=b.c)


------解决方案--------------------
SQL code
SELECT DISTINCT cdf1.*
FROM CongFuData AS cdf1
    INNER JOIN CongFuData AS cdf2
        ON cdf2.B = cdf1.B AND cdf2.C = cdf1.C AND cdf2.A <> cdf1.A

------解决方案--------------------
select * from congfudata as t
where exists(select 1 from congfudata where b=t.b and c=t.c and a<>t.a)

------解决方案--------------------
SQL code

SELECT * 
FROM
(SELECT A,B,C,D,E,F,G,
B_NUMBER=ROW_NUMBER()OVER(PARTITION BY B ORDER BY B),
C_NUMBER=ROW_NUMBER()OVER(PARTITION BY C ORDER BY C)
FROM CongFuData) 
WHERE B_NUMBER>1 AND C_NUMBER>1