请问:如何在同一块表中查询相同的记录?
例如下表:
CID1 CName CNumber CRe1 CManage - --------------------- -----------------
1 张三 20 smith asdf
2 张三1 21 smith asdf
3 张三2 22 smith asdf
4 张三3 23 smith asdf
5 张三 20 smith asdf
6 张三4 25 smith asdf
7 张三5 26 smith asdf
8 张三6 27 smith asdf请写入SQL语句
例如下表:
CID1 CName CNumber CRe1 CManage - --------------------- -----------------
1 张三 20 smith asdf
2 张三1 21 smith asdf
3 张三2 22 smith asdf
4 张三3 23 smith asdf
5 张三 20 smith asdf
6 张三4 25 smith asdf
7 张三5 26 smith asdf
8 张三6 27 smith asdf请写入SQL语句
declare @t table(CID1 int,CName varchar(10),CNumber int,CRe1 varchar(10),CManage varchar(10))
insert @t select 1,'张三',20,'smith','asdf'
insert @t select 2,'张三1',21,'smith','asdf'
insert @t select 3,'张三2',22,'smith','asdf'
insert @t select 4,'张三3',23,'smith','asdf'
insert @t select 5,'张三',20,'smith','asdf'
insert @t select 6,'张三4',25,'smith','asdf'
insert @t select 7,'张三5',26,'smith','asdf'
insert @t select 8,'张三6',27,'smith','asdf'
select *
from @t a
where exists(select 1 from @t where CName = a.CName and CID1 <> a.CID1)
/*
CID1 CName CNumber CRe1 CManage
----------- ---------- ----------- ---------- ----------
1 张三 20 smith asdf
5 张三 20 smith asdf
*/
from @t
group by cname
having count(1) > 1
/*
cname num
---------- -----------
张三 2
*/
from @t a
where exists(select 1 from @t where CName = a.CName and CID1 <> a.CID1)中间的1表示什么意思?