如何按案例和顺序排序?

brccelvz  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(301)

我要问下一张table

|id |Name|type      |
---------------------
|1  |B   |Secondary |
|2  |A   |Scholar   |
|3  |C   |University|

这可能是sql查询:

select * from invite order by  
        case "type" 
             when 'Scholar'   then 1 
             when 'Secondary' then 2
             when 'University' then 3
         end asc;

但现在我需要按案例排序:示例: when 'Scholar' then 1 desc 有可能吗?
我的目标是按id和案例类型排序示例:

|id |Name|type      |
---------------------
|1  |B   |Secondary |
|2  |B   |Secondary |
|3  |B   |Secondary |
|4  |A   |Scholar   |
|5  |A   |Scholar   |
|6  |C   |University|
|7  |A   |Scholar.  |

结果将是:

|id |Name|type      |
---------------------
|7  |A   |Scholar.  |
|5  |A   |Scholar   |
|4  |A   |Scholar   |
|3  |B   |Secondary |
|2  |B   |Secondary |
|1  |B   |Secondary |
|6  |C   |University|
r1wp621o

r1wp621o1#

你的问题表明你想在每个小组中找到一个新的方向:

order by (case "type" 
             when 'Scholar'   then 1 
             when 'Secondary' then 2
             when 'University' then 3
          end) asc;
         (case when "type" = Scholar then id end) desc,
         id asc;

但是,您的示例表明您只需要一秒钟 order by 钥匙, id desc .

ekqde3dh

ekqde3dh2#

我想你想要:

order by  
    case "type" 
         when 'Scholar'    then 1 
         when 'Secondary'  then 2
         when 'University' then 3
    end,
    id desc

也可以使用数组而不是条件逻辑:

order by
    array_position(array['Scholar', 'Secondary', 'University'], "type"),
    id desc

相关问题