我想查看一家公司的所有專案是否都處于已取消或待處理狀態，并且所有來源都只是費用，然后只接受這些公司。
Comb_id status_id status origin_id origin
1 6 Submitted 1 Premium
1 6 Submitted 1 Premium
2 6 Submitted 2 Fee
2 6 Submitted 1 Premium
2 6 Submitted 2 Fee
2 6 Submitted 1 Premium
3 6 Submitted 1 Premium
3 6 Submitted 1 Premium
4 1 Pending 2 Fee
4 1 Pending 2 Fee
4 1 Pending 2 Fee
4 1 Pending 2 Fee
4 1 Pending 2 Fee
4 1 Pending 2 Fee
下面的查詢作業正常并給出正確的結果 4，但這是否可以使用狀態和來源的 id 而不是描述，也比這個查詢更好。
SELECT
COMB_ID
FROM
(
SELECT
COMB_ID,
CASE
WHEN TRIM(REPLACE(REPLACE(REPLACE(STATUS, 'Cancelled', ''), 'Pending', ''), ',', '')) IS NULL THEN 1
ELSE 0
END AS STATUS,
CASE
WHEN TRIM(REPLACE(REPLACE(ORIGIN, 'Fee', ''), ',', '')) IS NULL THEN 1
ELSE 0
END AS ORIGIN
FROM
(
SELECT
COMB_ID,
LISTAGG(STATUS,
', ') WITHIN GROUP (
ORDER BY
STATUS) STATUS,
LISTAGG(ORIGIN,
', ') WITHIN GROUP (
ORDER BY
ORIGIN) ORIGIN
FROM
(
SELECT
COMB_ID,
STATUS_ID,
STATUS,
ORI_ID,
ORIGIN
FROM
COMPANY
WHERE
DETAILS = 1 )
GROUP BY
COMB_ID))
WHERE
STATUS = 1;
uj5u.com熱心網友回復：
使用基本聚合我們可以嘗試：
SELECT COMB_ID
FROM COMPANY
GROUP BY COMB_ID
HAVING COUNT(CASE WHEN STATUS NOT IN ('Cancelled', 'Pending')
THEN 1 END) = 0 AND
COUNT(CASE WHEN ORIGIN <> 'FEE' THEN 1 END) = 0;
該HAVING子句的第一部分斷言匹配公司的唯一狀態值是已取消和待定。第二部分斷言唯一的原始值是費用。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/408194.html
標籤：
上一篇：使用REGEX提取維度