请帮助编写查询。
我有三张桌子：
+-------------------+
| Patient |
| PatientPhysician |
| Physician |
+-------------------+
在一个PhysicalOrganizationID中查找名字、姓氏和出生日期相似的患者。
我将向您展示您更好地理解问题的数据：
mysql> SELECT pt.Id, pt.FirstName, pt.LastName, pt.DoB, ph.PhysicianOrganizationId
-> FROM Patient pt, Physician ph, PatientPhysician pp
-> WHERE pt.Id = pp.IdPatient AND ph.Id = pp.IdPhysician
-> ORDER BY pt.Id;
+----+-----------+-------------+------------+-------------------------+
| Id | FirstName | LastName | DoB | PhysicianOrganizationId |
+----+-----------+-------------+------------+-------------------------+
| 1 | Mario | Gotze | 1989-01-09 | 101 |
| 2 | Mario | Gotze | 1989-01-09 | 102 |
| 3 | Mario | Gotze | 1989-01-09 | 101 |
| 4 | Fillip | Gotze | 1989-01-09 | 101 |
| 5 | Marco | Rues | 1988-09-12 | 102 |
| 5 | Marco | Rues | 1988-09-12 | 101 |
| 5 | Marco | Rues | 1988-09-12 | 103 |
| 6 | Dimitri | Payet | 1986-10-10 | 101 |
| 7 | Dimitri | Payet | 1986-10-10 | 101 |
| 8 | Dimitri | Payet | 1986-10-10 | 101 |
| 8 | Dimitri | Payet | 1986-10-10 | 102 |
| 9 | Zlatan | Ibrahimovic | 1982-01-12 | 103 |
| 9 | Zlatan | Ibrahimovic | 1982-01-12 | 101 |
| 10 | Zlatan | Ibrahimovic | 1982-01-12 | 101 |
| 10 | Zlatan | Ibrahimovic | 1982-01-12 | 103 |
+----+-----------+-------------+------------+-------------------------+
15 rows in set (0.01 sec)
我写了一个查询，但结果不正确：
SELECT
pt.Id,
pt.FirstName,
pt.LastName,
pt.DoB,
ph.PhysicianOrganizationId
FROM Patient pt, Physician ph, PatientPhysician pp
WHERE pt.Id = pp.IdPatient AND ph.Id = pp.IdPhysician
GROUP BY pt.FirstName, pt.LastName, pt.DoB, ph.PhysicianOrganizationId
HAVING COUNT(*) > 1 ORDER BY pt.Id;
结果：
+----+-----------+-------------+------------+-------------------------+
| Id | FirstName | LastName | DoB | PhysicianOrganizationId |
+----+-----------+-------------+------------+-------------------------+
| 1 | Mario | Gotze | 1989-01-09 | 101 |
| 6 | Dimitri | Payet | 1986-10-10 | 101 |
| 9 | Zlatan | Ibrahimovic | 1982-01-12 | 103 |
| 9 | Zlatan | Ibrahimovic | 1982-01-12 | 101 |
+----+-----------+-------------+------------+-------------------------+
我需要这个结果：
+----+-----------+-------------+------------+-------------------------+
| Id | FirstName | LastName | DoB | PhysicianOrganizationId |
+----+-----------+-------------+------------+-------------------------+
| 1 | Mario | Gotze | 1989-01-09 | 101 |
| 3 | Mario | Gotze | 1989-01-09 | 101 |
| 6 | Dimitri | Payet | 1986-10-10 | 101 |
| 7 | Dimitri | Payet | 1986-10-10 | 101 |
| 8 | Dimitri | Payet | 1986-10-10 | 101 |
| 9 | Zlatan | Ibrahimovic | 1982-01-12 | 103 |
| 9 | Zlatan | Ibrahimovic | 1982-01-12 | 101 |
| 10 | Zlatan | Ibrahimovic | 1982-01-12 | 103 |
| 10 | Zlatan | Ibrahimovic | 1982-01-12 | 101 |
+----+-----------+-------------+------------+-------------------------+
告诉我我做错了什么？
最佳答案
SELECT pt.Id, tmp1.fname, tmp1.lname, tmp1.dob, tmp1.poid
FROM (
SELECT pt.FirstName AS fname,
pt.LastName AS lname,
pt.DoB as dob,
ph.PhysicianOrganizationId AS poid
FROM Patient pt, Physician ph, PatientPhysician pp
WHERE pt.Id = pp.IdPatient AND ph.Id = pp.IdPhysician
GROUP BY fname, lname, dob, poid
HAVING COUNT(*) > 1) AS tmp1
JOIN Patient AS pt ON pt.FirstName = tmp1.fname AND pt.LastName = tmp1.lname AND pt.DoB = tmp1.dob
JOIN PatientPhysician AS pp ON pt.Id = pp.IdPatient
JOIN Physician AS ph ON ph.Id = pp.IdPhysician AND tmp1.poid = ph.PhysicianOrganizationId
ORDER BY pt.Id;
关于mysql - MySQL ManyToMany显示重复的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19407848/