SQLZOO答案-The nobel table can be used to practice more subquery

本文主要是介绍SQLZOO答案-The nobel table can be used to practice more subquery，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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

SELECT * FROM nobel 
where winner = 'International Committee of the Red Cross'

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

SELECT  winnerFROM nobel
where subject = 'physics' AND yr = (SELECT yr FROM nobel WHERE winner = 'Toshihide Maskawa') 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)

注：Order by 按照时间排序（默认从小到大升序排序）可得出所有颁奖时间，limit 1则可得出首次颁奖时间。

 

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

SELECT distinct yrFROM nobel
WHERE subject = 'Physics'

 

5.哪幾年的得獎者人數多於12人呢? 列出得獎人數多於12人的年份，獎項和得獎者。

SELECT yr, subject, winnerFROM nobel
WHERE yr IN (SELECT yr FROM nobel GROUP BY yr HAVING count(winner) > 12 )

 

6.哪些得獎者獲獎多於1次呢？他們是哪一年獲得哪項獎項呢？ 列出他們的名字，獲獎年份及獎項。先按名字，再按年份順序排序。

SELECT winner, yr, subjectFROM nobel
WHERE winner IN (SELECT winner FROM nobel GROUP BY winner HAVING count(winner)>1)
ORDER BY winner, yr

 

这篇关于SQLZOO答案-The nobel table can be used to practice more subquery的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---