SQLZOO第3天NOBEL子查询

最新推荐文章于 2021-08-10 14:15:11 发布

VIP文章 weixin_46819300

最新推荐文章于 2021-08-10 14:15:11 发布

阅读量172

点赞数

分类专栏： SQLZOO

本文链接：https://blog.csdn.net/weixin_46819300/article/details/117567118

版权

紅十字國際委員會 (International Committee of the Red Cross) 曾多次獲得和平獎。試找出與紅十字國際委員會同年得獎的文學獎(Literature)得獎者和年份。

SELECT winner, yr FROM nobel
WHERE yr IN
(SELECT yr FROM nobel WHERE winner = 'International Committee of the Red Cross') AND subject = 'Literature'

日本物理學家益川敏英 (Toshihide Maskawa) 曾獲得物理獎。同年還有兩位日本人一同獲得物理獎。試列出這2位日本人的名稱。

SELECT winner FROM nobel
WHERE yr=(
select yr
from nobel
WHERE winner = 'Toshihide Maskawa') AND subject = 'Physics' AND winner <>'Toshihide Maskawa'

3.首次頒發的經濟獎 (Economics)的得獎者是誰?

SELECT winner 
  FROM nobel 
WHERE subject = 'Economics' 
  AND  yr = (SELECT yr FROM nobel WHERE subject = 'Economics'
  ORDER BY yr LIMIT 1)

4.哪幾年頒發了物理獎，但沒有頒發化學獎?

SELECT distinct yr
  FROM nobel
WHERE subject = 'Physics' AND yr NOT IN

关注