SQL自联接-SQL和Access

Question

表1

决策

ID	代码
1	TK01	是
2	OP01	---
3	TK01	---
4	MK02	是
5	MK02	---

在T-SQL中，我将使用可更新的CTE来执行此操作。使用分析函数，获得决策的正确值，并识别具有重复的代码，然后只更新那些重复的代码：

with u as (
select *,
Max(decision) over(partition by code) v, 
Count(*) over(partition by code) cnt
from t
)
update u
set decision = v
where cnt > 1 and decision = '---'

tsql:

UPDATE t1 SET t1.Decision = 'Yes' FROM Table1 t1 INNER JOIN Table1 t2 ON t1.Code = t2.Code
WHERE t1.Decision <> t2.Decision

您可以通过在块中添加UPDATE语句来观察预期的结果，而无需执行它

BEGIN TRAN
SELECT * FROM Table1
UPDATE t1 SET t1.Decision = 1 FROM Table1 t1 
INNER JOIN Table1 t2 ON t1.Code = t2.Code
WHERE t1.Decision <> t2.Decision
SELECT * FROM Table1
ROLLBACK TRAN