http://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial
1、selectyr,subject,winner
from nobel
where yr = 1950
2.、select winner
from nobel
where yr='1962' and subject='Literature'
说明：where语句中对表示条件的需要用单引号。
3、select yr,subject
from nobel
where winner='Albert Einstein'
4、select winner
from nobel
where subject= 'Peace'and yr>='2000'
5、selectyr,subject,winner
from nobel
where subject='Literature'and yr between1980 and 1989
6、selectyr,subject,winner
from nobel
where winner in ('TheodoreRoosevelt','Woodrow Wilson','Jimmy Carter','Barack Obama')
7、select winner
from nobel
where winner like 'John%'
8、selectyr,subject,winner
from nobel
where subject='Physics' and yr=1980
or (subject='Chemistry'and yr='1984')
或者
Select * from nobel
Where subject='Physics' and yr=1980
or (subject='Chemistry'and yr='1984')
9、select * fromnobel
where subject not in ('Chemistry','Medicine')and yr='1980'
10、select * fromnobel
where subject= 'Medicine' and yr<'1910'
or subject= 'Literature' and yr>='2004'
11、select * fromnobel
where winner='PETER GRÜNBERG'
说明：元音字母“Ü”输入方法为“alt+0220”即可。
12、select * fromnobel
where winner='EUGENE O''NEILL'
说明：条件语句中的“EUGENE O'NEILL”因为含有单引号，需与sql语句中的单引号区分，所以在本身名称单词中已经包含的单引号使用两个单引号，即为'EUGENE O''NEILL'
13、selectwinner,yr,subject from nobel
where winner like 'Sir%'
order by yr desc,winner desc
或者select winner,yr,subject from nobel
where winner like 'Sir%'
order by yr desc,winner
说明：原题中要求先显示最新获奖者，再同年按照名称顺序排序，则表示时间顺序排序为降序，即用“desc”，
14、原题：Show the 1984winners and subject ordered by subject and winner name; but list Chemistry andPhysics last.
Select winner,subject from nobel
WHERE YR='1984'
order by subjectin('Chemistry','Physics'),subject,WINNER
或者
SELECT winner, subject, subject IN('Physics','Chemistry')
FROM nobel
WHERE yr=1984
ORDER BY subject,winner
说明：
使用ORDER
BY配合IN语句
原题要求按照项目subject和获奖者姓名winner排序，同时要求获奖项目为Physics和Chemistry的排在最后。
Subject in（）配合order
by 语句的意思是subject in（’Physics’,’Chemistry’）进行判断，如果属于项目Physics或属于项目Chemistry，则为真返回数值“1”，如果不属于，则为假返回数值“0”，按照默认的asc升序排序，则判断为真返回数值为“1”的会排在最后，所以项目为Physics和Chemistry的自然就会排在最后。同时order by逗号后面的内容“，subject，winner”意为 将剩下不属于Physics和Chemistry的数据按照默认升序排序asc；姓名也按照默认升序排序asc。
参考资料：
MySQL
ORDER BY 排序 IF 及IN
https://blog.csdn.net/bestallen/article/details/53726192
����@�3�;#�6�
网友评论