我需要一个查询来比较基于相同id的表行值,并相应地更新Status列.如果相同ID的值匹配,则将状态更新为已匹配或未匹配
ID | Value | Status |
---|---|---|
1 | AAA | MATCHED |
2 | BBB | MATCHED |
3 | CCC | NOT MATCHED |
1 | AAA | MATCHED |
2 | BBB | MATCHED |
3 | CXX | NOT MATCHED |
我需要一个查询来比较基于相同id的表行值,并相应地更新Status列.如果相同ID的值匹配,则将状态更新为已匹配或未匹配
ID | Value | Status |
---|---|---|
1 | AAA | MATCHED |
2 | BBB | MATCHED |
3 | CCC | NOT MATCHED |
1 | AAA | MATCHED |
2 | BBB | MATCHED |
3 | CXX | NOT MATCHED |
您可以避免自联接操作,并使用几个窗口函数来判断id上的max与id上的min值何时相同.
SELECT ID,
Status_,
CASE WHEN MIN(Value_) OVER(PARTITION BY ID)
= MAX(Value_) OVER(PARTITION BY ID)
THEN 'Matched'
ELSE 'Not Matched'
END AS Status_
FROM tab
如果您已经有一个状态值,并且需要更新它,则可以在UPDATE语句中使用相同的查询,如下所示:
WITH cte AS (
SELECT tab.*,
CASE WHEN MIN(Value_) OVER(PARTITION BY ID)
= MAX(Value_) OVER(PARTITION BY ID)
THEN 'Matched'
ELSE 'Not Matched'
END AS newStatus
FROM tab
)
UPDATE cte
SET Status_ = newStatus
WHERE NOT Status_ = newStatus;
Output:
ID | Value_ | Status_ |
---|---|---|
1 | AAA | Matched |
1 | AAA | Matched |
2 | BBB | Matched |
2 | BBB | Matched |
3 | CCC | Not Matched |
3 | CXX | Not Matched |
查看演示here.