查询出完全相同记录的sql语句？

一个表：   
  table1:   
        id     b         c       d   
        1     er       tt     ee   
        2     er       tt     ee   
        3     sw       bb     cc   
        4     ww       xx     aa   
        5     ww       xx     aa   
  现在我想得到这个表中除id外别的字段值完全相同的记录，谢谢   
  如结果：   
        id     b         c       d   
        1     er       tt     ee   
        2     er       tt     ee   
        4     ww       xx     aa   
        5     ww       xx     aa 

10 个解决方案

#1

select a.* fdrom table1 a
inner join
  (
   select b,c,d from table1 group by b,c,d having count(*)>1
  ) b
  on a.b=b.b and a.c=b.c and a.d=b.d

#2

select *
from table1 t
where exists(select * from table1 where id<>t.id and b=t.b and c=t.c and d=t.d)

#3

麻烦问一下删除怎么写 是不是这样
delete a.* fdrom table1 a
inner join
  (
   select b,c,d from table1 group by b,c,d having count(*)>1
  ) b
  on a.b=b.b and a.c=b.c and a.d=b.d
能不能给我个我们通常写的格式 不要SQL转换过的格式，谢谢了

#4

------------------------------------
-- Author:  happyflystone  
-- Date:2008-12-22 21:39:57
------------------------------------

-- Test Data: TA
IF OBJECT_ID('TA') IS NOT NULL 
    DROP TABLE TA
Go
CREATE TABLE TA(id INT,b NVARCHAR(2),c NVARCHAR(2),d NVARCHAR(2))
Go
INSERT INTO TA
SELECT 1,'er','tt','ee' UNION ALL
SELECT 2,'er','tt','ee' UNION ALL
SELECT 3,'sw','bb','cc' UNION ALL
SELECT 4,'ww','xx','aa' UNION ALL
SELECT 5,'ww','xx','aa' 
GO
--Start
SELECT 
	a.* 
FROM 
	TA a
left join ta b on a.id <> b.id and checksum(a.b,a.c,a.d) = checksum(b.b,b.c,b.d)
where b.id is not null


--Result:
/*
(1 行受影响)
id          b    c    d
----------- ---- ---- ----
1           er   tt   ee
2           er   tt   ee
4           ww   xx   aa
5           ww   xx   aa

(4 行受影响)

*/
--End 

#5

引用 3 楼 ztall 的回复:

麻烦问一下删除怎么写 是不是这样 
delete a.* fdrom table1 a 
inner join 
  ( 
  select b,c,d from table1 group by b,c,d having count(*)>1 
  ) b 
  on a.b=b.b and a.c=b.c and a.d=b.d 
能不能给我个我们通常写的格式 不要SQL转换过的格式，谢谢了 

通常格式？


删除　：

--Start
delete a
from ta as a
where exists(select 1 from ta where id <> a.id and checksum(a.b,a.c,a.d) = checksum(b,c,d))

SELECT 
    a.* 
FROM 
    TA a



--Result:
/*
id          b    c    d
----------- ---- ---- ----
3           sw   bb   cc

(1 行受影响)

*/
--End 

#6

select * from table1 a where exists(select * from table1 where b=a.b and c=a.c and d=a.d and id<>a.id)

#7

我用1楼的方法查询出来了，怎么删除呢，谢谢

#8

select * from table1 a where (select count(*) from table1 where b=a.b and c=a.c and d=a.d)>1

#9

引用 7 楼 ztall 的回复:

我用1楼的方法查询出来了，怎么删除呢，谢谢

create table tb(id int, b varchar(10), c varchar(10), d varchar(10))
insert into tb values( 1 , 'er' , 'tt' , 'ee') 
insert into tb values( 2 , 'er' , 'tt' , 'ee') 
insert into tb values( 3 , 'sw' , 'bb' , 'cc') 
insert into tb values( 4 , 'ww' , 'xx' , 'aa') 
insert into tb values( 5 , 'ww' , 'xx' , 'aa')
go

--search
select * from tb t where exists(select 1 from (select b , c, d from tb group by b,c,d having count(*) > 1) n where b = t.b and c = t.c and d = t.d)
/*
id          b          c          d          
----------- ---------- ---------- ---------- 
1           er         tt         ee
2           er         tt         ee
4           ww         xx         aa
5           ww         xx         aa

（所影响的行数为 4 行）
*/

--delete
delete tb from tb t where exists(select 1 from (select b , c, d from tb group by b,c,d having count(*) > 1) n where b = t.b and c = t.c and d = t.d)
select * from tb
/*
id          b          c          d          
----------- ---------- ---------- ---------- 
3           sw         bb         cc

（所影响的行数为 1 行）
*/

drop table tb 

#10

delete from table1  where id in(select a.id from table1 a
inner join
  (
   select b,c,d from table1 group by b,c,d having count(*)>1
  ) b
  on a.b=b.b and a.c=b.c and a.d=b.d)