有条件订购 [英] Conditional Order By

问题描述

我有一张桌子，那里有一组带有开始标签和结束标签的路线。每行都有一个列 progres，这是在其中应用全局 order by子句的列，最后是一个选择列，用于指示必须对哪些标签类型进行排序（奇数，偶数或全部）。
，如果LabelStart> LabelEnd =>由ASC排序，则由DESC

I have a table where there is a set of routes with a start label and end label. each row has a column "progres" which is the column where to apply the global "order by" clause, and finally a selection column that tells which label types must be ordered (Odd, Even or All). if LabelStart > LabelEnd => order by ASC else by DESC

例如，以下是路径路径

Routes
ID  RouteID, Progres, LabelStart, LabelEnd  Type 
1   1        5            1          21      O
2   1        10           10          2      E 
4   2        15           2          25      A
5   3        20           1          11      O
6   3        22           4          10      E
7   4        30           5          11      O
8   4        31           2          12      E

，这里属于路线的点

Points
PoinID  RouteID, Label
1          1        3           
2          1        2
4          1        1 
5          1        8 
6          1        5 
7          1        6 
8          1        9
9          1        21 
10         1        10 
11         1        11 
12         2        1
13         2        2
14         2        12
15         2        3
16         2        25
17         2        14
...  

我需要一个表，其中所有点均由Routes Proges进行全局排序，根据类型按偶数，奇数或全部分组，如果LabelStart最终按ASC排序>通过DESC结束标签。结果应该是：

What I need is a table where all the points are ordered globally by Routes Proges, grouped by Even, Odd or All based on types and finally ordered by ASC if LabelStart > LabelEnd else by DESC. the result shoud be:

ID  RouteID, PointID
1     1        4           
2     1        1
4     1        6 
5     1        8
6     1        11
7     1        9
8     1        10
9     1        5
10    1        7
11    1        2
12    2        13 
13    2        15
...

推荐答案

SQL小提琴

select 
    row_number() over() id, *
from (
    select
        r.routeid,
        p.pointid,
        label,
        type,
        labelstart,
        labelend
    from
        route r
        inner join
        point p on p.routeid = r.routeid
    where
        r.type = 'E' and p.label % 2 = 0
        or
        r.type = 'O' and p.label % 2 != 0
        or
        r.type = 'A'
    order by
        r.routeid, r.progres, r.id,
        case labelstart < labelend
            when true then label
            else label * - 1
        end
) s

这篇关于有条件订购的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！