我想創建一個 SQL 查詢來識別沒有 A3 事件的井。我正在使用 SQL 服務器。
我嘗試了多種方法，例如檢查計數，與事件 A3 進行比較，但仍然無法獲得我想要的結果。
從下面的示例所需的結果將是 W3 和 W4
| 地點 | 事件 |
|---|---|
| W1 | A1 |
| W1 | A2 |
| W1 | A3 |
| W2 | A1 |
| W2 | A2 |
| W2 | A3 |
| W3 | A1 |
| W3 | A4 |
| W4 | A2 |
| W4 | A4 |
uj5u.com熱心網友回復：
聚合是一種方法：
SELECT Site
FROM yourTable
GROUP BY Site
HAVING COUNT(CASE WHEN Event = 'A3' THEN 1 END) = 0;
如果您確實還需要查看匹配井的所有站點，請將上述內容放入 CTE 并將其用作子查詢：
WITH cte AS (
SELECT Site
FROM yourTable
GROUP BY Site
HAVING COUNT(CASE WHEN Event = 'A3' THEN 1 END) = 0
)
SELECT Site, Event
FROM yourTable
WHERE Site IN (SELECT Site FROM cte);
uj5u.com熱心網友回復：
NOT EXISTS是一種非常高效的方法
例子
Select *
From YourTable A
Where not exists ( select 1
from YourTable T
where T.Well=A.Well and T.[Event]='A3'
)
結果
Well Event
W3 A1
W3 A4
W4 A2
W4 A4
uj5u.com熱心網友回復：
你可以做
SELECT DISTINCT site
FROM table_name
WHERE site NOT IN (SELECT DISTINCT site
FROM table_name
WHERE event = 'A3')
uj5u.com熱心網友回復：
該sub-query會回傳與A3事件的網站。因此，使用LEFT JOIN和過濾子查詢中不可用的站點加入表
SELECT DISTINCT Site
FROM your_table as tbl
LEFT JOIN
(
SELECT DISTINCT Site
FROM your_table
WHERE Event = 'A3'
) as sq
ON tbl.site=sq.site
WHERE sq.Site IS NULL
uj5u.com熱心網友回復：
SELECT Well
FROM test
where Well not in (select Well from tablename where Evt = 'A3');
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/371557.html
標籤：sql sql-server 查询语句