05sql必知必会笔记NBA带你认识sql子查询

作者: singworld 分类: Sql 发布时间: 2019-07-28 18:30

— 哪个球员身高最高,最高身高是多少?

SELECT t.player_name,t.height FROM player t
WHERE height = (SELECT MAX(height) FROM player)

— 查询每个队中大于平均身高的球员有哪些

SELECT player_name,height,team_id FROM player a
WHERE height > (SELECT AVG(height) FROM player b WHERE
a.team_id = b.team_id)

— 想看出场的球员有哪些

SELECT player_name,player_id,team_id
FROM player
WHERE EXISTS(
    SELECT player_id FROM player_score
    WHERE player.player_id = player_score.player_id
)

SELECT player_name,player_id,team_id
FROM player
WHERE NOT EXISTS(
    SELECT player_id FROM player_score
    WHERE player.player_id = player_score.player_id
)

— 出场过的球员都有哪些in

SELECT player_id,team_id,player_name
FROM player WHERE player_id in
(SELECT player_id FROM player_score
WHERE player.player_id = player_score.player_id)

— 实际上在查询过程中,在我们对 cc 列建立索引的情况下,我们还需要判断表 A 和表 B 的大小。在这里例子当中,表 A 指的是 player 表,表 B 指的是 player_score 表。如果表 A 比表 B 大,那么 IN 子查询的效率要比 EXIST 子查询效率高,因为这时 B 表中如果对 cc 列进行了索引,那么 IN 子查询的效率就会比较高。
— 同样,如果表 A 比表 B 小,那么使用 EXISTS 子查询效率会更高,因为我们可以使用到 A 表中对 cc 列的索引,而不用从 B 中进行 cc 列的查询。

— 比印第安纳步行者队中任何队员身高高的球员信息

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)

— 查询每个球队球员数

SELECT team_name,(SELECT COUNT(*) FROM player WHERE 
player.team_id = team.team_id) player_num
FROM team

— 场均得分大于20分的球员

SELECT player_id,player_name,team_id FROM player
WHERE player_id IN (SELECT player_id FROM player_score
GROUP BY player_id HAVING AVG(score)>20)

Leave a Reply

Your email address will not be published. Required fields are marked *