我正在撰寫一個 SQL 查詢，其中我有一個名為記錄的表，在記錄表中我們有兩列 Security 和 Position。我想撰寫一個查詢來完成以下兩個查詢
當同一證券同時擁有多頭和空頭時，我們需要洗掉所有多頭并僅加載空頭。
當同一證券只有多頭頭寸而沒有空頭頭寸時，我們不想洗掉任何多頭頭寸，我們需要加載同一證券的所有多頭頭寸。
預期結果 ：
對于第 1 點，我創建了一個 SQL 查詢并且它作業正常，但對于第 2 點，我沒有找到可以幫助我的方法。
這是我寫的SQL：
DELETE a
FROM Records a
INNER JOIN
(SELECT [Security]
FROM Records
GROUP BY [Security]
HAVING COUNT(*) > 1) b ON a.[Security] = b.[Security]
AND a.position = 'Long';
uj5u.com熱心網友回復：
我們可以嘗試在 CTE 中查找所有具有多頭和空頭頭寸的證券。然后，洗掉所有具有這兩種型別頭寸以及頭寸多頭的證券。
WITH cte AS (
SELECT Security
FROM Records
WHERE Position IN ('Short', 'Long')
GROUP BY Security
HAVING MIN(Position) <> MAX(Position)
)
DELETE
FROM Records
WHERE Position = 'Long' AND
Security IN (SELECT Security FROM cte);
uj5u.com熱心網友回復：
您想要洗掉所有存在相同證券的“短”行的“長”行。為此使用EXISTS或。IN
delete from records
where position = 'Long'
and security in (select security from records where position = 'Short');
uj5u.com熱心網友回復：
with cte as (
select Security, count(distinct(Position)) as cnt
from records
group by Security
)
delete from records
where Position = 'Long'
and Security = (select Security from cte where cnt = 2);
供參考檢查： https ://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e20f5769b10150663cc13a2e79cb9d53
uj5u.com熱心網友回復：
我會考慮使用存在。
DELETE a
FROM Records a
WHERE Position = 'Long'
AND EXISTS (
SELECT 1
FROM Records b
WHERE b.Security = a.Security
AND b.Position = 'Short'
);
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/432422.html
上一篇：選擇包含唯一值的行（非冗余）
下一篇：如果以前出現過，則SQL不同