如何排序行的“组”?

vom3gejh  于 2021-07-29  发布在  Java
关注(0)|答案(1)|浏览(190)

以下是查询:

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

我想对每个worktypeworkid的行块进行排序,将levelid较低的块放在块的顶部。
下面是我想要得到的结果:

WorkTypeId  WorktypeWorkID  LevelID
    NULL        NULL            NULL    
    NULL        NULL            NULL        
    1           3               1       // blocks which have MinLevel 1
    1           5               1   
    4           6               1
    1           4               1       // blocks which have MinLevel 2
    1           4               2   
    3           19              2
    4           17              1
    4           17              2
    4           18              1
    4           18              2       
    1           1               1       // blocks which have MinLevel 3
    1           1               2
    1           1               3
    1           2               1
    1           2               2
    1           2               3
    4           7               1
    4           7               2
    4           7               3
gdrx4gfi

gdrx4gfi1#

我想这就是你想要的:

SELECT WorkTypeId, WorktypeWorkID, LevelID, MAX(LevelID) OVER (PARTITION BY WorktypeWorkID) as maxLevelID
FROM Worktypes as w
LEFT JOIN WorktypesWorks as ww on w.ID = ww.WorktypeID
LEFT JOIN WorktypesWorksLevels as wwl on ww.ID = wwl.WorktypeWorkID

ORDER BY maxLevelID

相关问题