mysql - 更新分区/组中最新记录中的列

Question

问候，节日快乐!

我是 SQL 新手，我正在尝试解决一个问题。我用谷歌搜索但找不到解决方案。如果这是在错误的论坛中，我请求您将其移至适当的论坛。

预先感谢您给我的任何见解!欣赏它!

我的 table :

ID1        ID2 RECNBR ACTFLG                    
ABDCDDFDD  1   1      0                 
JHKLLLKHJ  9   1      0                
SDFKJLSDK  4   1      0              
LKJHJNHGJ  9   2      1 <-- Error (only the record with highest RECNBR for a ID2 can have ACTFLG as 1                     
SDFDSLLSD  2   1      0               
BDFDFDFDD  1   2      1                    
DSFKLKJSD  4   2      1            
LOKKJLLLH  9   3      1           
GFLDSFFGL  2   2      1

一些事实:

1. ID1 是一个唯一的字母数字列，对于每条记录都是唯一的。
2. ID2 数字值，但不一定按排序顺序。
3. RECNBR - 每个 ID2 从 1 开始，对于同一 ID2 的每个新记录加 1。
4. ACTFLG - 对于同一 ID2 内的最高 RECNBR，该字段设置为“1”。所有其他记录均应为零。
5. 记录未按任何排序顺序。

问题:

1. 如果您注意到 ID2“9”。它有 3 条记录，RECNUM 为 1、2 和 3。根据事实，只有 RECNUM '3' 的 ACTFLG 为 '1'。然而，在我的表中，我既有 RECNUM '2'，也有 '3' 和 ACTFLG '1'。

要求:

注意:我试图避免循环。我有一个使用循环的解决方案，并且执行需要花费大量时间。我想看看是否可以使用 PARTITION/GROUP BY 等系统函数通过一些简单的 1 或 2 行代码来完成此操作。

我可以将所有记录的 ACTFLG 设置为“0”。我需要关于如何识别特定 ID2 中具有最高 RECNUM 的记录的编码想法。一旦确定，我想将这些记录的 ACTFLG 更新为“1”。

或者

识别 ID2“9”、RECNUM“2”(有问题)等记录，并将 ACTFLG 更新为“0”。

Answer 1

假设使用 SQL Server，这就是锤子:

with cte as (
   select *, row_number() over (partition by ID2 order by recnbr desc) as rn
)
update cte
set actflg = case when rn = 1 then 1 else 0 end;

这是手术刀:

with cte as (
   select *, row_number() over (partition by ID2 order by recnbr desc) as rn
)
update cte
set actflg = 1
where rn = 1
   and actflg = 0;

with cte as (
   select *, row_number() over (partition by ID2 order by recnbr desc) as rn
)
update cte
set actflg = 0
where rn <> 1
   and actflg = 1;

无论您采用哪种方法，您都应该只需要将此作为一次性清理即可。展望 future ，最好正确地维持该值。

您没有要求它，但对设计有一些观察:

1. 如果每个 ID2 组只能有一项处于事件状态，请使用唯一的筛选索引强制执行该操作。类似于 在 [myTable] (ID2) 上创建唯一索引 [myFilteredIndex]，其中 (actflg = 1);
2. 只要给定表中的其他信息，actflg 就是多余的，我可以导出任何行的 actflg 值。我承认将其存储在行中可能是一种性能优化，但它确实带来了维护它的开销(正如这个问题的整个存在理由所暗示的那样)。值得尝试一下动态导出哪个是事件的并完全删除此列。