我有一個看起來像這樣的查詢：
SELECT
cid,
COALESCE(newcust.newcust, false) AS newcust,
COALESCE(svipstat.svipstat, false) AS svipstat,
COALESCE(vipstat.vipstat, false) AS vipstat
FROM customer c
LEFT JOIN (
SELECT cid, true AS newcust
FROM customer c
WHERE active IS TRUE AND created_at >= current_date - interval '1 day' * 30
) AS newcust ON newcust.cid = c.cid
LEFT JOIN (
SELECT cid, true AS svipstat
FROM customer c
WHERE active IS TRUE AND (select count(1) from visits where cid = customer.cid and vdate >= current_date - interval '30 days') >= (SELECT value FROM criteria WHERE c = 'svipstat' AND criteria.pid = c.pid)
) AS svipstat ON svipstat.cid = c.cid
LEFT JOIN (
SELECT cid, true AS vipstat
FROM customer c
WHERE active IS TRUE AND (select count(1) from visits where cid = customer.cid and vdate >= current_date - interval '30 days') >= (SELECT value FROM criteria WHERE c = 'vipstat' AND criteria.pid = c.pid)
) AS vipstat ON vip.cid = c.cid;
這個查詢回傳的內容是這樣的：
cid | newcust | svip | vipstat
--------------------------------
1. true. false. false
2. false. true. false
3. false. false. true
我的理想情況是，如果我可以讓列回傳一個“字串”，基于我擁有的 LEFT JOINS，例如：
cid | status
-------------
1. newcust
2. svip
3 vipstat
有沒有辦法做到這一點？？？我一直在努力尋找解決方案:(
uj5u.com熱心網友回復：
您可以像這樣使用CASE WHEN邏輯來選擇status外部查詢中的值
SELECT
cid,
CASE WHEN COALESCE(newcust.newcust, false)
THEN 'newcust'
WHEN COALESCE(svipstat.svipstat, false)
THEN 'svipstat'
WHEN COALESCE(vipstat.vipstat, false)
THEN 'vipstat'
END status
FROM ...
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/504843.html
標籤：sql PostgreSQL