查出雷同数据

查出相同数据

SQL code

-- 查出 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')-- 求教

SQL code

select * from congfudata as twhere 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 tWHERE  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 bwhere 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    ga2   b    c    d2   e2   f    ga3   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