日期:2014-05-17 浏览次数:20857 次
-- 查出 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)
------解决方案--------------------
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)
------解决方案--------------------
SELECT * FROM congfudata AS t WHERE EXISTS (SELECT 1 FROM congfudata WHERE b = t.b AND c = t.c AND a <> t.a)
------解决方案--------------------
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 */
------解决方案--------------------
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的判断。
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)
------解决方案--------------------
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)
------解决方案--------------------
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