如何查询相同的记录？
表格如下：
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
.. .. .. ..
想查询相同num1/num2/num3的记录：
如 id 2和id 7
[解决办法]
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
