title : 每日深耕,勤练不缀之sql子查询
本文应用sql数据均从github上下载
需要的小伙伴自取https://github.com/cystanford/sql_nba_data.git
查询结果集
关联子查询: 子查询需要执行多次,采用循环的方式,先从外部查询开始,每次传入子查询进行查询,再将结果反馈给外部
<-- more! -->
我们想要查找球队中大于平均身高的球员有哪些?并显示他们的球员姓名身高和所在球队ID
SELECT player_name,height,team_id
FROM player AS a
WHERE height >
(SELECT AVG(height)
FROM player AS b
WHERE a.team_id =b.team_id)
每次查询都得执行一次子语句
通常关联子查询也会和EXISTS一起使用
比如我们想看出场过的球员有哪些?并且显示他们的姓名,id,球队ID
是否出场可以通过player_score此表来查询,因为在player_score有出场记录
SELECT player_id,player_name,team_id
FROM player
WHERE EXISTS
(SELECT player_id FROM player_score
WHERE player.player_id =player_score.player_id)
IN和EXISTS虽然查询结果一样,但是效率确不一样,
如果A表比B表小,EXISTS效率高,因为查询时可以使用A表对于play_id的索引,反之,则IN效率更高(小表驱动大表更有效率)
ANY ,ALL
举例:
想要查询球员表中,比印第安纳步行者team_id=002那只队中任意一个球员身高高的所有球员信息
SELECT player_id,player_name,height
FROM player
WHERE height >
ANY (SELECT height
FROM player
WHERE team_id =1002)
SELECT player_id,player_name,height
FROM player
WHERE height >
ALL (SELECT height
FROM player
WHERE team_id =1002)
将子查询作为主查询的计算字段(把子查询的结果当做主查询的列)
比如我想查询每个球队的球员数,也就是对应那个team_id ,相同的team_id在player这张表中球员数量是多少
SELECT team_name,(SELECT count(*) FROM player WHERE player.team_id=team.team_id)
AS player_num
FROM team
AS player_num
是给
SELECT count(*) FROM player WHERE player.team_id=team.team_id
起了个别名,方便我们使用
最后出个题:
编写SQL语句,得到场均得分大于20的球员(场均得分从player_score表中获取,同时你需要输出球员的ID,球员姓名及所在球队的ID信息)
我的理解是:
1.场均得分大于20,第一场得了40分,第二场得了2分,场均为21分照样满足条件
2.一场比赛中,球员可以出现多次
解析思路:先得出球员在每场比赛中的总分数,然后对球员按比赛分组,计算球员在所参加比赛中的平均分数
SELECT t2.player_id,t2.player_name,t2.team_id,t3.v
FROM player AS t2
JOIN(SELECT t1.player_id,avg(t1.total) AS v
FROM
(SELECT player_id,SUM(score) AS total FROM player_score WHERE game_id IN (SELECT game_id FROM player_score GROUP BY game_id)
GROUP BY player_id #对球员进行分组,计算在每场比赛的总分数
)AS t1
GROUP BY t1.player_id #对球员分组,计算每场比赛的均分
HAVING v>20 #对分组进行筛选
)AS t3 ON t2.player_id =t3.player_id
本题有点难度!!!!
要经常回来复盘!!
答案贴在这
**非关联子查询:**只作为主查询的条件语句执行一次
我们想要找nba球员中谁最高?
且输出他的姓名
SELECT player_name,height
FROM player
WHERE height=(SELECT MAX(height) FROM player)