我有以下查询:
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
试试这个:
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句