我有一張包含帳戶和付款型別的表格。每個賬戶必須有 1 種支付型別“GOGW”，1 個賬戶可能有多種支付型別。
我需要找到一種方法來確定哪些帳戶沒有“GOGW”
表格1
| 帳戶 | 型別 |
|---|---|
| 123456 | 查看 |
| 123456 | GOGW |
| 654321 | 直接借記 |
| 654321 | GOGW |
| 987654 | 直接借記 |
| 456789 | BACS |
應該回傳
| 帳戶 | 型別 |
|---|---|
| 987654 | 直接借記 |
| 456789 | BACS |
uj5u.com熱心網友回復：
您可以使用 GROUP BY / HAVING 找出沒有任何 GOGW 的帳戶
SELECT Account
FROM table1
GROUP BY Account
HAVING COUNT(CASE WHEN [Type] = 'GOGW' THEN 1 END) = 0
如果你想找到那些不完全是一個：
SELECT Account
FROM table1
GROUP BY Account
HAVING COUNT(CASE WHEN [Type] = 'GOGW' THEN 1 END) <> 1
現在您可以使用 IN、EXISTS 或 JOIN 來獲取這些帳戶的型別：
SELECT a.*
FROM table1 t1
JOIN (
SELECT Account
FROM table1
GROUP BY Account
HAVING COUNT(CASE WHEN [Type] = 'GOGW' THEN 1 END) = 0
) AS t2
ON t1.Account = t2.Account
如果您使用的是最新版本，則可以改用視窗函式：
select Account, Type
from (
select Account, Type, COUNT(CASE WHEN [Type] = 'GOGW' THEN 1 END) OVER (PARTITION BY ACCOUNT) as cnt
from table1
) as t
WHERE cnt = 0;
小提琴
uj5u.com熱心網友回復：
您可以嘗試以下陳述句...
SELECT table1.[Account]
,table1.[Type]
FROM table1
LEFT OUTER JOIN (SELECT table1.[Account]
FROM table1
WHERE table1.[Type] = 'GOGW') GOGW ON table1.[Account] = GOGW.[Account]
WHERE table1.[Type] <> 'GOGW'
AND GOGW.[Account] IS NULL
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/514557.html
上一篇：如何替換正則運算式？