我有一張如下表。我想為特定的 columnA 和 columnB 挑選那些只“需要運行”的行。在下面的示例中，結果應該只是第 3 行。
columnA columnB columnC
123 abc needs to run
123 abc suceeded
111 abc needs to run
uj5u.com熱心網友回復：
我們可以嘗試在子查詢中使用帶有條件聚合的視窗函式，然后count只比較哪個'needs to run'
SELECT t1.columnA,
t1.columnB,
t1.columnC
FROM (
SELECT t1.*,
COUNT(*) OVER(PARTITION BY columnA,columnB) cnt1,
COUNT(CASE WHEN columnC = 'needs to run' THEN 1 END) OVER(PARTITION BY columnA,columnB) cnt2
FROM T t1
) t1
WHERE t1.cnt1 = t1.cnt2
uj5u.com熱心網友回復：
您可以使用：
SELECT columnA,
columnB,
columnC
FROM (
SELECT columnA,
columnB,
columnC,
SUM(CASE WHEN columnC = 'needs to run' THEN 0 ELSE 1 END)
OVER (PARTITION BY columnA, columnB) AS has_other
FROM table_name
)
WHERE has_other = 0;
uj5u.com熱心網友回復：
SELECT table1.*
FROM table1
INNER JOIN (
SELECT columnA, columnB
FROM table1
GROUP BY columnA, columnB
HAVING count(*) = 1
) sub ON table1.columnA = sub.columnA
AND table1.columnB = sub.columnB
AND table1.columnC = 'needs to run'
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/488344.html
上一篇：Sql觸發器不起作用
下一篇：獲得小時間隔之間的銷售額