SELECT中子查询

嵌套查询

分类

关键字

子查询作为主查询的列

数据地址：https://github.com/cystanford/sql_nba_data<br><br>非关联子查询<br>查询哪个球员身高最高<br>SELECT player_name, height<br>FROM player<br>WHERE height = (SELECT MAX(height) FROM player)<br><br><br>关联子查询<br>查找球队中大于平均身高的球员有哪些，并且显示他们的球员姓名，身高以及所在球队ID<br>SELECT player_name, height, team_id<br>FROM player AS a<br>WHERE height &gt; (SELECT AVG(height) FROM player AS b WHERE b.team_id = a.team_id)<br><br><br>查看出场过的球员都有哪些<br>SELECT player_id, team_id, player_name<br>FROM player<br>WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)<br><br><br>集合比较子查询<br>查询出场球员<br>SELECT player_id, team_id, player_name<br>FROM player<br>WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_socre.player_id)<br><br><br>抽象模式：<br>SELECT * FROM A WHERE cc IN (SELECT cc FROM B) <br>SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc = A.cc)<br><br>// 如果表A比表B大，效率上：IN &gt; EXIST，<br>// 因为B表中如果对cc列进行了索引，IN的子查询效率会比较高<br><br>// 如果表A比表B小，效率上：EXIST &gt; IN<br>// 因为A表中如果对cc列进行了索引<br><br><br>查询球员表中，比印第安纳步行者(team_id=1002)中任何一个球员身高高的球员的信息<br>并且输出球员的ID，姓名，身高<br>SELECT player_id, player_name, height<br>FROM player<br>WHERE height &gt; ANY(SELECT height FROM player WHERE team_id=1002)<br><br>比印第安纳步行者(team_id=1002)中所有球员都高的球员的信息<br>SELECT player_id, player_name, height<br>FROM player<br>WHERE height &gt; ALL(SELECT height FROM player WHERE team_id=1002)<br><br><br><br>子查询作为主查询的列<br>查询每个球队的球员数 // 作为列，需要起个别名player_num<br>SELECT team_name, (SELECT COUNT(*) FROM player WHERE player.team_id = team.team_id) AS player_num<br>FROM team<br><br><br>练习：<br>查询场均得分大于20的球员信息，包括球员的ID，姓名，球队ID的信息<br>SELECT player_id, player_name, team_id<br>FROM player<br>WHERE player_id IN <br> (SELECT player_id <br> FROM player_score <br> GROUNP BY player_score.player_id<br> HAVING AVG(player_score.score) &gt; 20)<br>