如何使用 SQL 顯示患者 ID、名字、姓氏和主治醫師的專業？
顯示 Patient_id、first_name、last_name 和主治醫師的專業。
僅顯示診斷為“癲癇”且醫生名字為“麗莎”的患者
檢查患者、入院和醫生表格以獲取所需資訊。
SELECT a.first_name ,a.last_name,a.patient_id FROM patients a
JOIN admissions AS n ON a.patient_id= n.patient_id
WHERE n.diagnosis='Epilepsy'
UNION
SELECT specialty
FROM admissions AS n
JOIN physicians AS p ON n.attending_physician_id=p.physician_id
WHERE p.first_name='Lisa'
ORDER BY RAND()
LIMIT 6;
患者表
patient_id INT
first_name TEXT
last_name TEXT
gender CHAR(1)
birth_date DATE
city TEXT
primary key icon province_id CHAR(2)
allergies TEXT
height INT
weight INT
錄取表
patient_id INT
admission_date DATE
discharge_date DATE
diagnosis TEXT
primary key icon attending_physician_id INT
物理表
physician_id INT
first_name TEXT
last_name TEXT
specialty TEXT
uj5u.com熱心網友回復：
這是一個解決方案。您還應該查看資料的顯示方式（大小寫混合），甚至是長描述（例如 Epilepsy ABC）。
select i.patient_id, i.first_name, i.last_name,
coalesce(p.specialty,'not provided') as physician_specialty --coalesce in case you decide to left join at some point
from patients i
join admission a
on i.patient_id = a.patient_id
join physicians p
on a.attending_physician_id = p.physician_id
where upper(a.diagnosis) like '%EPILEPSY%'
and upper(p.first_name) = 'LISA'
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/516982.html
標籤：mysqlsql左连接
上一篇：獲取鍵出現在所有一組值旁邊的記錄