SQLZOO平台上有一道题,题目如下:
要求:查找1984年获奖者和主题,按主题和获胜者名称排序,并把化学奖和物理奖排到最后面显示
难点:把化学奖和物理奖排到最后面显示,这种自定义排序不能简单使用升序ASC或降序DESC
猴子老师的答案和解释如下:
-- 答案
select winner, subject
from nobel
where yr=1984
order by subject in ('Physics','Chemistry'),subject,winner;
sql解释:
subject in ('Physics','Chemistry')返回值(0或者1),
会对每一个subject做一个if的判断,有的是1,没有的是0
再用order by把这些值排序在下面
这两个科目('Physics','Chemistry')的就是0排在前边,
是这两个科目的返回1就排在后边了。
因为化学和物理科目题目要求在后面,所以引入此函数出现0、1,达成题目的要求
解题的关键是subject in ('Physics','Chemistry'),order by 根据返回值进行排序,这样就可以实现自定义排序。
根据以上原理,对下面题目举一反三:
-- sname中h开头的排最前面,其余的升序排列
select sname
from test
order by sname like 'h%' desc ,sname asc;
sql解释:
sname like 'h%'返回值(0或者1),
首先对每一个sname做一个if的判断,h开头的是1,非h开头的是0
再用order by把这些值降序排列
这样h开头的排在前边
同理,如果要求h开头的排最先,然后是z开头的,其余的按升序排序,则可再加一个判断条件:
-- sname中h开头的排最前面,然后是z开头的,其余的升序排列
select sname
from test
order by sname like 'h%' desc ,sname like 'z%' desc ,sname asc;