有什么问题,欢迎评论或私聊。转载请私聊博主,谢谢。
原题链接:https://sqlzoo.net/wiki/Self_join
其他题解连接:https://blog.csdn.net/aiqiyizz/article/details/109057732
题解对应的是英文版题目。
文章目录
- 3 SELECT from Nobel
- 3.1 Winners from 1950
- 3.2 1962 Literature
- 3.3 Albert Einstein
- 3.4 Recent Peace Prizes
- 3.5 Literature in the 1980's
- 3.6 Only Presidents
- 3.7 John
- 3.8 Chemistry and Physics from different years
- 3.9 Exclude Chemists and Medics
- 3.10 Early Medicine, Late Literature
- 3.11 Umlaut
- 3.12 Apostrophe
- 3.13 Knights of the realm
- 3.14 Chemistry and Physics last
3 SELECT from Nobel
3.1 Winners from 1950
SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950
3.2 1962 Literature
SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = 'Literature'
3.3 Albert Einstein
SELECT yr, subject
FROM nobel
WHERE winner = 'Albert Einstein'
3.4 Recent Peace Prizes
SELECT winner
FROM nobel
WHERE yr >= 2000 AND subject = 'Peace'
3.5 Literature in the 1980’s
SELECT *
FROM nobel
WHERE yr BETWEEN 1980 AND 1989 AND subject = 'Literature'
3.6 Only Presidents
SELECT * FROM nobel
WHERE winner IN ('Theodore Roosevelt',
'Woodrow Wilson',
'Jimmy Carter',
'Barack Obama')
3.7 John
SELECT winner
FROM nobel
WHERE winner LIKE 'John%'
3.8 Chemistry and Physics from different years
SELECT *
FROM nobel
WHERE (yr = 1980 AND subject = 'physics')
OR (yr = 1984 AND subject = 'chemistry')
3.9 Exclude Chemists and Medics
SELECT *
FROM nobel
WHERE yr = 1980 AND subject NOT IN ('Chemistry ', 'Medicine')
3.10 Early Medicine, Late Literature
SELECT *
FROM nobel
WHERE (yr < 1910 AND subject = 'Medicine')
OR (yr >= 2004 AND subject = 'Literature')
3.11 Umlaut
SELECT *
FROM nobel
WHERE winner = 'PETER GRÜNBERG'
3.12 Apostrophe
SELECT *
FROM nobel
WHERE winner = 'EUGENE O\'NEILL'
3.13 Knights of the realm
SELECT winner, yr, subject
FROM nobel
WHERE winner LIKE 'Sir%'
ORDER BY yr DESC, winner
3.14 Chemistry and Physics last
先按照第一项排序可以使得物理和化学排到末尾
SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Physics','Chemistry'),subject,winner