mysql - 多列的条件排序依据

Question

我有以下查询:

SELECT year, month, btype, bnumber 
FROM some_table 
ORDER BY year ASC, month ASC, bnumber ASC, btype ASC 

btype 有以下值

block
week
month

当 btype = block 和 bnumber = 1 我希望它是表中的第一个值。但是，当 btype = block 和 bnumber = 2 时，我希望它位于所有其他 bnumber = 2 值之后。

这是查询当前返回值的方式:

year    month   btype   bnumber
======  ======  ======  ======
2017    3       block   1
2017    3       month   1
2017    3       week    1
2017    3       block   2
2017    3       week    2
2017    3       week    3
2017    3       week    4

这就是我希望的结果:

year    month   btype   bnumber
======  ======  ======  ======
2017    3       block   1
2017    3       month   1
2017    3       week    1
2017    3       week    2
2017    3       block   2
2017    3       week    3
2017    3       week    4

使用 CASE 可以实现这样的事情吗？我尝试了几种不同的变体，但似乎无法正常工作。这是我试图使 btype = block 和 bnumber = 2 被视为 bnumber = 3 但它没有工作:

ORDER BY year ASC, month ASC, 
         CASE btype 
            WHEN 'block' and bnumber = '2' THEN bnumber = '3' 
            ELSE 1 
         END ASC, 
         bnumber ASC, 
         btype ASC

Answer 1

试试这个:

SELECT year, month, btype, bnumber
FROM some_table
ORDER BY year ASC, month ASC, 
         CASE 
            -- Place btype ='block' / bnumber = '1' at the first place
            WHEN btype ='block' and bnumber = '1' THEN 1
            -- Prioritize bnumber = '2' / btype <> 'block' over 
            -- bnumber = '2' / btype = 'block'
            WHEN bnumber = '2' THEN CASE 
                                      WHEN btype <> 'block' THEN 1.1
                                      ELSE 1.2
                                    END            
            ELSE bnumber  
         END ASC, 
         bnumber ASC, 
         btype ASC

Demo here