如何将标志设置为组的最大数目？

cxfofazt 于 2021-07-29 发布在 Java

关注(0)|答案(3)|浏览(412)

以下是查询：

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是group by中的最大值时，设置值为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

sql sql-server group-by Max

来源：https://stackoverflow.com/questions/62286266/how-to-set-a-flag-to-the-max-number-of-a-group

3条答案

按热度按时间

ztigrdn81#

这有点棘手，因为 NULL 价值观。所以我想你想要：

(case when levelid = max(levelid) over (partition by WorkTypeId, WorktypeWorkID) and
           WorktypeWorkID is not null
      then 1 else 0
 end) as maxlevel

赞(0）回复(0）举报 2021-07-29

holgip5t2#

可以使用窗口函数：

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

赞(0）回复(0）举报 2021-07-29

46scxncf3#

这只是一个 CASE 表达式和窗口 MAX ，就像我在评论中提到的：

SELECT V.WorkTypeId,
       V.WorktypeWorkID,
       V.LevelID,
       CASE V.LevelID WHEN MAX(V.LevelID) OVER (PARTITION BY V.WorkTypeId, WorktypeWorkID) THEN 1 ELSE 0 END AS MaxLevel
FROM (VALUES(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))V(WorkTypeId,WorktypeWorkID,LevelID);

赞(0）回复(0）举报 2021-07-29

我来回答

如何将标志设置为组的最大数目？

3条答案

相关问题

热门标签

最新问答