SQL查询多个admission_date相同的患者id

Question

我正在查询同一诊断多次入院的患者。

Show patient_id, diagnosis from admissions。查找因同一诊断多次入院的患者。

SELECT
patient_id,
diagnosis,admission_date
FROM 
admissions
GROUP BY
diagnosis,
admission_date
HAVING
COUNT(admission_date) > 1;

我想也许子查询会是更好的解决方案。但是不知道如何查询相同诊断的patient_id s

Answer 1

无子查询:

SELECT a1.patient_id, a1.diagnosis
FROM admissions a1
JOIN admissions a2
ON a1.patient_id = a2.patient_id AND a1.diagnosis = a2.diagnosis
GROUP BY a1.patient_id, a1.diagnosis
HAVING COUNT(*) > 1

与子查询:

SELECT patient_id, diagnosis
FROM admissions
WHERE patient_id IN (
SELECT patient_id
FROM admissions
GROUP BY patient_id, diagnosis
HAVING COUNT(*) > 1
)

希望有帮助。