SQL 查询以查找出生日期相同的员工对
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5027194/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query to find pairs of employees who have the same birthdate
提问by dali1985
I have a table EMPLOYEE with 100 people. I would like to write a query to find pairs of employees who have the same BIRTHDATE.
我有一个有 100 人的表 EMPLOYEE。我想编写一个查询来查找具有相同 BIRTHDATE 的员工对。
The result should return EMPNO, LASTNAME and BIRTHDATE for each employee in the pair (a 6-column result table).
结果应该为该对中的每个员工返回 EMPNO、LASTNAME 和 BIRTHDATE（一个 6 列的结果表）。
I thought something like that
我想这样的事情
SELECT t1.EmpNo
,t1.LastName
,t1.BirthDate
,t2.EmpNo
,t2.LastName
,t2.BirthDate
FROM Employee t1
INNER JOIN (
SELECT EmpNo
,LastName
,BirthDate
FROM Employee ) t2 ON t2.BirthDate = t1.BirthDate
WHERE t2.EmpNo != t1.EmpNo
Do you think it is correct?
你认为正确吗？
回答by anothershrubery
I would just do:
我只会做：
SELECT t1.EmpNo,
t1.LastName,
t1.BirthDate,
t2.EmpNo,
t2.LastName,
t2.Birthdate
FROM Employee t1, Employee t2
WHERE t1.BirthDate = t2.Birthdate
AND t1.EmpNo <> t2.EmpNo
回答by user617850
It looks correct but I would remove the second select.. sub query
它看起来正确，但我会删除第二个 select.. 子查询
SELECT
t1.EmpNo ,t1.LastName ,t1.BirthDate ,t2.EmpNo ,t2.LastName ,t2.BirthDate
FROM Employee t1
INNER JOIN Employee t2 ON t2.BirthDate = t1.BirthDate
WHERE t2.EmpNo != t1.EmpNo
回答by Karol Borkowski
I would also add the condition that t1.EmpNo<t1.EmpNOto avoid repetions.
我还要添加t1.EmpNo<t1.EmpNO避免重复的条件。
回答by Kiritharan Nithya
Here is my answer
这是我的答案
Select name, date From employees Group by date of birth Having count(*)>1;
Select name, date From employees Group by date of birth Having count(*)>1;
