我有 3 張表 Doctor、Patient、DoctorPatient。醫生有欄位 ID、姓名、電話。患者有 ID、姓名、電話、年齡、性別欄位。DoctorPatient 包含 DoctorID 和 PatienId，這是 Doctor 和 Patient 表之間的鏈接。我如何找到所有患者都在 30 歲以下的醫生？
表之間有關系
uj5u.com熱心網友回復：
如果按醫生分組，則患者總數必須與 30 歲以下的患者總數相同。
SELECT Doctor.name
, COUNT(IIF(Patient.age < 30, Patient.ID, null)) AS TotalPatientsUnder30
, COUNT(Patient.ID) AS TotalPatients
FROM Doctor
JOIN DoctorPatient AS dopa ON dopa.DoctorID = Doctor.ID
JOIN Patient ON Patient.ID = dopa.PatientID
GROUP BY Doctor.ID, Doctor.name
HAVING COUNT(Patient.ID) = COUNT(IIF(Patient.age < 30, Patient.ID, null))
AND COUNT(Patient.ID) > 0
uj5u.com熱心網友回復：
給出 sql：
SELECT Doctors.DoctorName, Max(Patients.age) AS MaxOfage
FROM Patients INNER JOIN (Doctors INNER JOIN DoctorPatient ON Doctors.ID = DoctorPatient.DoctorID) ON Patients.ID = DoctorPatient.PatientID
GROUP BY Doctors.DoctorName
HAVING (((Max(Patients.age))<30));
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/364367.html
上一篇：VBA訪問-測量顯示單位