# 怎么查询相同的记录

www.MyException.Cn  网友分享于：2013-02-18  浏览：58次

id   num1   num2   num3
1       1         3           2
2       2         2           1
3       2         1           1
4       1         1           2
5       2         2           2
6       3         3           1
7       2         2           1
..     ..       ..         ..

------解决方案--------------------
select * from 表 t where exists(select 1 from 表 where id <> t.id and num1=t.num1 and num2=t.num2 and num3=t.num3)
------解决方案--------------------
SELECT DISTINCT A.* FROM TABLE A INNER JOIN TABLE B ON A.ID <> B.ID AND A.num1=B.num1 AND A.num2=B.num2 AND A.num3=B.num3

SELECT DISTINCT A.* FROM TABLE A INNER JOIN TABLE B ON A.ID <> B.ID
CAST(ISNULL(A.num1,0) AS VARCHAR(50))+CAST(ISNULL(A.num2,0) AS VARCHAR(50))+CAST(ISNULL(A.num3,0) AS VARCHAR(50))=CAST(ISNULL(B.num1,0) AS VARCHAR(50))+CAST(ISNULL(B.num2,0) AS VARCHAR(50))+CAST(ISNULL(B.num3,0) AS VARCHAR(50))
------解决方案--------------------
SELECT *
FROM Table1
WHERE convert(varchar(10),num1)+ ', '+convert(varchar(10),num2)+ ', '+convert(varchar(10),num3)
IN (SELECT convert(varchar(10),num1)+ ', '+convert(varchar(10),num2)+ ', '+convert(varchar(10),num3) FROM Table1 GROUP BY num1,num2,num3 having count(*)> 1)
------解决方案--------------------

select * from T as tmp
where (select count(*) from T where num1=tmp.num1 and num2=tmp.num2 and num3=tmp.num3)> 1