一尘不染

如果另一列具有特定值,则删除所有具有唯一 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

阅读 95

收藏
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