有没有人能帮我写出这个sql语句？找出数据表中重复数据

Question

create table #c
(
id int identity(1,1),           --标识ID
ExamineeName nvarchar(50),      --考生姓名
CertificateNumber nvarchar(50), --考生身份证
AllowExamNumber nvarchar(50),   --考生准考证
EnrolTypeId int                 --考生考试科目
)
insert into #c values('a','123','001','1')
insert into #c values('a','123','001','2')
insert into #c values('a','123','001','3')
insert into #c values('b','456','002','1')
insert into #c values('b','456','002','2')
insert into #c values('c','789','003','1')
insert into #c values('d','0123','001','1')
insert into #c values('d','0123','001','2')
insert into #c values('d','0123','001','3')问题：例如此表，考生a和d的准考证号相同了。
但是我通过什么sql语句判断出来他们准考证相同了呢？

Answer 1

select id,AllowExamNumber,count(*) 
from #c
group by id,AllowExamNumber 
having count(*)>1

Answer 2

LZ理解这个having count(*)>1 就行了，条件自己添加吧！

Answer 3

表的自关联实现Select T1.ID, T1.ExamineeName, T1.CertificateNumber, T1.AllowExamNumber,
       T1.EnrolTypeId, T2.ID, T2. ExamineeName
From #C As T1 Left Join #C AS T2 On (T1.AllowExamNumber = T2.AllowExamNumber 
                                     And T1.EnrolTypeId = T2.EnrolTypeId)

Answer 4

SELECT a.ExamineeName,b.ExamineeName 
FROM #c  a
INNER JOIN #c b ON a.AllowExamNumber = b.AllowExamNumber AND a.ExamineeName <> b.ExamineeName
GROUP BY a.ExamineeName,b.ExamineeName/*
ExamineeName ExamineeName
a d
d a*/

Answer 5

create table #c
(
id int identity(1,1),           --标识ID
ExamineeName nvarchar(50),      --考生姓名
CertificateNumber nvarchar(50), --考生身份证
AllowExamNumber nvarchar(50),   --考生准考证
EnrolTypeId int                 --考生考试科目
)insert into #c values('a','123','001','1')
insert into #c values('a','123','001','2')
insert into #c values('a','123','001','3')
insert into #c values('b','456','002','1')
insert into #c values('b','456','002','2')
insert into #c values('c','789','003','1')
insert into #c values('d','0123','001','1')
insert into #c values('d','0123','001','2')
insert into #c values('d','0123','001','3')select a.* 
from #c a
inner join 
(select AllowExamNumber
from #c
group by AllowExamNumber
having count(distinct ExamineeName)>1) b on a.AllowExamNumber=b.AllowExamNumberid          ExamineeName CertificateNumber AllowExamNumber EnrolTypeId
----------- ------------ ----------------- --------------- -----------
1           a            123               001             1
2           a            123               001             2
3           a            123               001             3
7           d            0123              001             1
8           d            0123              001             2
9           d            0123              001             3

Answer 6

--这样？
select * from #c a where exists(
select 1 from #c 
where id<>a.id and AllowExamNumber=a.AllowExamNumber and ExamineeName=a.ExamineeName
)
order by AllowExamNumber/*
id          ExamineeName                                       CertificateNumber                                  AllowExamNumber                                    EnrolTypeId
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------
1           a                                                  123                                                001                                                1
2           a                                                  123                                                001                                                2
3           a                                                  123                                                001                                                3
7           d                                                  0123                                               001                                                1
8           d                                                  0123                                               001                                                2
9           d                                                  0123                                               001                                                3
4           b                                                  456                                                002                                                1
5           b                                                  456                                                002                                                2(8 行受影响)

Answer 7

--改下，应该是这样
select * from #c a where exists(
select 1 from #c where AllowExamNumber=a.AllowExamNumber and ExamineeName<>a.ExamineeName
)
order by AllowExamNumber/*
id          ExamineeName                                       CertificateNumber                                  AllowExamNumber                                    EnrolTypeId
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------
1           a                                                  123                                                001                                                1
2           a                                                  123                                                001                                                2
3           a                                                  123                                                001                                                3
7           d                                                  0123                                               001                                                1
8           d                                                  0123                                               001                                                2
9           d                                                  0123                                               001                                                3(6 行受影响)