如果另一列具有特定值，则删除所有具有唯一 ID 的行

小编典典

如果另一列具有特定值，则删除所有具有唯一 ID 的行 - SQL Server

sql

如果标签为“错误”，我需要删除所有PaymentId 实例。

CREATE TABLE Test
(Id INT, PaymentId INT, Label VARCHAR(25));

INSERT INTO Test
VALUES
(1, 22, 'Error'),
(2, 22, 'Seattle'),
(3, 22, 'Pending'),
(4, 33, 'Paid'),
(5, 33, 'Los Angeles'),
(6, 44, 'Houston'),
(7, 44, 'Error'),
(8, 55, 'Pending'),
(9, 55, 'San Diego'),
(10, 55, 'Authorization')

SELECT * FROM Test

Id	PaymentId	Label
1	22	Error
2	22	Seattle
3	22	Pending
4	33	Paid
5	33	Los Angeles
6	44	Houston
7	44	Error
8	55	Pending
9	55	San Diego
9	55	Authorization

预期输出：

Id	PaymentId	Label
4	33	Paid
5	33	Los Angeles
8	55	Pending
9	55	San Diego
9	55	Seattle

阅读 120

2022-07-21

共1个答案

小编典典

通过 CTE 和窗口函数的另一种选择sum() over()

;with cte as (
SELECT * 
      ,Flg = sum( case when Label='Error' then 1 end) over (partition by PaymentID)
 FROM Test
)
Delete from cte where Flg >=1

更新的表格

2022-07-21