时间戳

首页 > 解决方案 > 如何将标志设置为组的最大数量？

sql - 如何将标志设置为组的最大数量？

问题描述

这是查询：

SELECT WorkTypeId, WorktypeWorkID, LevelID
    FROM Worktypes as w
    LEFT JOIN WorktypesWorks as ww on w.ID = ww.WorktypeID
    LEFT JOIN WorktypesWorksLevels as wwl on ww.ID = wwl.WorktypeWorkID

这是结果：

WorkTypeId  WorktypeWorkID  LevelID
1           1               1
1           1               2
1           1               3
1           2               1
1           2               2
1           2               3
1           3               1
1           4               1
1           4               2
1           5               1
NULL        NULL            NULL
3           19              2
4           6               1
4           7               1
4           7               2
4           7               3
4           17              1
4           17              2
4           18              1
4           18              2
NULL            NULL        NULL

我想仅在 LevelID（对于具有相同 WorkTypeId 的每个 WorktypeWorkID 组）所在的行添加一个名为“MaxLevel”的新列，当 LevelID 是组中的 MAX 编号时设置值为 1，否则为 0。

这是我想要得到的结果：

WorkTypeId  WorktypeWorkID  LevelID     MaxLevel
1           1               1           0
1           1               2           0
1           1               3           1 // 3 is the max on the group
1           2               1           0
1           2               2           0
1           2               3           1 // 3 is the max on the group
1           3               1           1 // 1 is the max on the group
1           4               1           0
1           4               2           1 // 2 is the max on the group
1           5               1           1 // 1 is the max on the group
NULL        NULL            NULL        0
3           19              2           1 // 2 is the max on the group
4           6               1           1 // 1 is the max on the group
4           7               1           0
4           7               2           0
4           7               3           1 // 3 is the max on the group
4           17              1           0
4           17              2           1 // 2 is the max on the group
4           18              1           0
4           18              2           1 // 2 is the max on the group
NULL            NULL        NULL        0

标签： sqlsql-servergroup-bymax

解决方案

您可以使用窗口函数：

SELECT
  WorkTypeId, 
  WorktypeWorkID, 
  LevelID,
  case when LevelId = max(LevelId) over(partition by WorktypeWorkID) then 1
       else 0
  end as MaxLevel
FROM Worktypes as w
LEFT JOIN WorktypesWorks as ww on w.ID = ww.WorktypeID
LEFT JOIN WorktypesWorksLevels as wwl on ww.ID = wwl.WorktypeWorkID

推荐阅读