联接到同一个表以修复不正确的数据

Question

我想创建一个显示不正确数据的报告，我需要找到一种方法来连接一个具有相似数据但不同标识符的表。标识符必须相同。所以我想使用MAX((来获得最高的数字标识符，然后更新记录以反映这一点。

所以我的数据看起来有点像这样：

ID        PRE   IDENTIFIER
123456789 ABC   1176109555
123456789 ABC   1430842555
123456789 ABC   1572290555
123456789 ABC   1651845555
123456789 ABC   2099393555
111111111 ABC   5555393555

因此，如果一个ID有多个标识符，我希望它不会像这样返回：

IDENTIFIER CORRECTED IDENTIFIER
1176109555 2099393555
1430842555 2099393555
1572290555 2099393555
1651845555 2099393555

左列不包括max((标识符，而右列则为每个列出的列都包含该max((标识。111111111不包括在内，因为它没有一个以上的标识符

Answer 1

使用CTE获得每个用户的最大id：

with max_identifier as (
select id, max(identifier) as corrected_identifier
from my_table
group by id 
)
select t.id, t.pre, t.identifier, m.corrected_identifier
from my_table t
join max_identifier m
on t.id = m.id 

刚刚意识到您提到了sql server。有人可能需要纠正我的语法，对不起。

with max_identifier (id, corrected_identifier) 
as (
select id, max(identifier) as corrected_identifier
from my_table
group by id 
)
select t.id, t.pre, t.identifier, m.corrected_identifier
from my_table t
join max_identifier m
on t.id = m.id 

Answer 2

尝试以下操作：

Select * From 
(
Select IDENTIFIER,
Max(IDENTIFIER) Over (Partition By ID) as CORRECTED_IDENTIFIER
From your_table
) D
Where D.IDENTIFIER<>CORRECTED_IDENTIFIER

Where clause中的条件D.IDENTIFIER<>CORRECTED_IDENTIFIER将确保不返回最大IDENTIFIER值，并且不返回不超过一个值的IDENTIFIER。

如果要更新原始表格，请使用以下CTE:

With CTE As
(
Select * From 
(
Select IDENTIFIER,
Max(IDENTIFIER) Over (Partition By ID) as CORRECTED_IDENTIFIER
From your_table
) D
Where D.IDENTIFIER<>CORRECTED_IDENTIFIER
)
Update CTE Set IDENTIFIER = CORRECTED_IDENTIFIER;

查看db<gt；小提琴。