SQLZOO-SELECT from Nobel Tutorial

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�

推荐阅读更多精彩内容

  • rljs by sennchi Timeline of History Part One The Cognitiv...
    sennchi阅读 4,848评论 0 10
  • **2014真题Directions:Read the following text. Choose the be...
    又是夜半惊坐起阅读 5,388评论 0 22
  • 【一】 “摆渡人”这几年势头一直很猛啊!似乎可以看到很多人朝摆渡人的船只挤来了。 2016近年末,由阿里影业、泽东...
    灵渠清韵阅读 112评论 0 0
  • 在我们的工作或生活中总是会面临很多的变化和不确定性。例如:公司组织结构变化,团队成员变化,失去亲人等等。面对变化,...
    李文艳Avivi阅读 346评论 0 1
  • 1 微风最近为一件事很困扰。 她屁股上的荧光不如以前亮了。 这放在以前不是什么特别大不了的事,毕竟她还是个非常年轻...
    puzzle圈儿阅读 59评论 0 0