我正在寫一個查詢，它應該回傳一份報告，包括在該機構作業的公共衛生作業者總數、人們在該機構接受的總劑量以及計劃在該機構中接種疫苗的人的總劑量在將來。我將任務分為三個 SQL 代碼：
-- 在該機構作業的公共衛生作業者總數為 1 人
SELECT `name`, address, phone, type, phone, capacity, COUNT(person_id) AS totalWorkers
FROM healthcare_worker
INNER JOIN facility ON facility.`name`= healthcare_worker.facility_name
GROUP BY healthcare_worker.facility_name
-- 2 人們在設施中接受的總劑量
SELECT `name`, address, phone, type, phone, capacity, SUM(dose) AS totalDose
FROM vaccination
INNER JOIN facility ON facility.`name`= vaccination.location
GROUP BY vaccination.location
-- 3 未來人們在設施中計劃接種的總劑量
SELECT `name`, address, phone, type, phone, capacity, COUNT(booking_id) AS totalBookings
FROM booking
INNER JOIN facility ON facility.`name`= booking.facility_name
GROUP BY booking.facility_name
我可以將它們放在一個查詢中嗎？
uj5u.com熱心網友回復：
您可以使用相關子查詢
SELECT `name`, address, phone, `type`, capacity
, ( SELECT COUNT(person_id)
FROM healthcare_worker
WHERE fac.`name`= healthcare_worker.facility_name
) AS totalWorkers
, ( SELECT SUM(dose)
FROM vaccination
WHERE fac.`name`= vaccination.location
) AS totalDose
, ( SELECT COUNT(booking_id)
FROM booking
WHERE fac.`name`= booking.facility_name
) AS totalBookings
FROM facility AS fac
| 姓名 | 地址 | 電話 | 型別 | 容量 | 總工人數 | 總劑量 | 總預訂量 |
|---|---|---|---|---|---|---|---|
| 最后護理 | 威尼斯 | 00 00 04 | 停尸房 | 4 | 3 | 15 | 3 |
| 韋瓦克 | xxx | 79 72 04 | 私人的 | 69 | 3 | 6 | 3 |
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/362587.html
上一篇：將cmdlet回應存盤在變數中時的PowerShell編碼問題
下一篇：獲取作業日函式以在星期日回傳整數