SQLZOO SELECT 来自诺贝尔 #14

Question

理科物理化学后不知道名字怎么排序!

Question: The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.

Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

SELECT winner, subject, subject IN('Physics', 'Chemistry')
FROM nobel
WHERE yr=1984
ORDER BY CASE
 WHEN subject IN ('Physics', 'Chemistry') = 0 THEN subject IN ('Physics', 'Chemistry')
 WHEN subject IN ('Physics', 'Chemistry') =  THEN winner
 ELSE winner 
End

欲知详情，请访问问题网址 http://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial

Answer 1

我在 SQLZOO 中尝试过，并且遵循 SQL 给出了正确的结果。

SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Physics','Chemistry'), subject, winner