create table demo11 (dn int, rn varchar(max))
insert into demo11 values(1,'A'),(1,'A-1'),(1,'A-3'),(2,'A'),(2,'B'),(2,'C'),(3,'A-3'),(3,'A-4'),(4,'A'),(4,'A-1'),(4,'A-2'),(4,'A-5')
DROP TABLE #dnrn
CREATE TABLE #dnrn(dn int,rn VARCHAR(MAX))
insert into #dnrn(dn,rn)
SELECT dn AS dn, MAX(rn) AS rn FROM demo11 GROUP BY dn
SELECT * FROM #dnrn
delete from demo11 where dn NOT IN (SELECT dn FROM #dnrn) AND rn NOT IN (SELECT rn FROM #dnrn)
SELECT * FROM demo11
嘗試了上面的查詢，但沒有得到想要的結果
預期結果：demo11 應僅由 dn 的最大修訂記錄組組成
輸出應該是：(1 A-3), (2 C), (3 A-4), (4, A-5)
uj5u.com熱心網友回復：
您在臨時表中有每個dnfrom demo11，因此dn NOT IN (SELECT dn FROM #dnrn)對于其中的每一行都是 false demo11，因此沒有行被洗掉。
當您加入派生表（不需要臨時表）時，您可以實作您想要做的事情，該表使用row_number()視窗函式為rn每個dn值的值分配數字，然后為該數字不是的所有行進行篩選1, 表示最大值。
DELETE d
FROM demo11 AS d
INNER JOIN (SELECT dn,
rn,
row_number() OVER (PARTITION BY dn
ORDER BY rn DESC) r
FROM demo11) AS x
ON d.dn = x.dn
AND d.rn = x.rn
WHERE x.r <> 1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/393890.html
標籤：sql sql-server sql-删除