我需要在where我的count選擇中應用一個子句，而不是名稱、房間代碼或日期。
這是我目前的查詢，它適用where于整個陳述句：
SELECT
l.name AS room_name,
l.room_code,
COUNT(res.*) AS nofixeditems,
COUNT(m.*) AS noinmaintenance,
rc.actiondate AS last_checked,
concat(u.firstname, ' ', u.surname) AS checked_by
FROM
resources res
FULL JOIN room_checklist rc ON (rc.assetid = res.aid)
FULL JOIN users u ON (u.uid = rc.actionuserid)
FULL JOIN locations l ON (l.locationid = rc.locationid)
FULL JOIN resource_locations rl ON (rl.location_id_fixed = l.locationid)
FULL JOIN maintenance.maintenance_log m ON (res.aid = m.asset_id)
WHERE
res.archived IS NOT TRUE AND
rl.location_id_fixed = l.locationid::bigint AND
res.hide_from_room_checklist IS NOT TRUE
GROUP BY
l.name,
l.room_code,
rc.actiondate,
checked_by
我需要它來顯示房間名稱、代碼等的整個串列，同時在資料不可用的計數上顯示 null。
uj5u.com熱心網友回復：
如果您的意思是在聚合函式中設定計算條件，您可以使用 filter (where conditions...)
Postgres 檔案
SELECT
l.name AS room_name,
l.room_code,
COUNT(res.*) FILTER (WHERE ...) AS nofixeditems,
COUNT(m.*) FILTER (WHERE ...) AS noinmaintenance,
rc.actiondate AS last_checked,
concat(u.firstname, ' ', u.surname) AS checked_by
FROM
resources res
FULL JOIN room_checklist rc ON (rc.assetid = res.aid)
FULL JOIN users u ON (u.uid = rc.actionuserid)
FULL JOIN locations l ON (l.locationid = rc.locationid)
FULL JOIN resource_locations rl ON (rl.location_id_fixed = l.locationid)
FULL JOIN maintenance.maintenance_log m ON (res.aid = m.asset_id)
WHERE
res.archived IS NOT TRUE AND
rl.location_id_fixed = l.locationid::bigint AND
res.hide_from_room_checklist IS NOT TRUE
GROUP BY
l.name,
l.room_code,
rc.actiondate,
checked_by
uj5u.com熱心網友回復：
嘗試使用子查詢
SELECT
l.name AS room_name,
l.room_code,
(Select COUNT(*) from resources where ID = res.id AND ..) AS nofixeditems,
(Select COUNT(*) from maintenance.maintenance_log where ID = m.id AND ..) AS noinmaintenance,
rc.actiondate AS last_checked,
concat(u.firstname, ' ', u.surname) AS checked_by
FROM
resources res
FULL JOIN room_checklist rc ON (rc.assetid = res.aid)
FULL JOIN users u ON (u.uid = rc.actionuserid)
FULL JOIN locations l ON (l.locationid = rc.locationid)
FULL JOIN resource_locations rl ON (rl.location_id_fixed = l.locationid)
FULL JOIN maintenance.maintenance_log m ON (res.aid = m.asset_id)
WHERE
res.archived IS NOT TRUE AND
rl.location_id_fixed = l.locationid::bigint AND
res.hide_from_room_checklist IS NOT TRUE
GROUP BY
l.name,
l.room_code,
rc.actiondate,
checked_by
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/355165.html
標籤：sql PostgreSQL