首页 > 试题广场 >

如下有一张球员信息表（info），字段依次为：球员id、球员

[单选题]

如下有一张球员信息表（info），字段依次为：球员id、球员名称、体重（球员的id和名称均为唯一索引）
+------+------+--------+
| id | name | weight |
+------+------+--------+
| 1 | A1 | 49 |
| 2 | A2 | 65 |
| 3 | B3 | 55 |
| 4 | T1 | 60 |
| 5 | G2 | 43 |
| 6 | C0 | 55 |
+------+------+--------+
还有一张比赛信息表（game），字段依次为：id、球员id、进球数目
+------+-----------+-------+
| id | person_id | count |
+------+-----------+-------+
| 1 | 3 | 10 |
| 2 | 1 | 24 |
| 3 | 6 | 9 |
| 4 | 1 | 2 |
| 5 | 2 | 11 |
| 6 | 5 | 23 |
| 7 | 3 | 15 |
| 8 | 2 | 16 |
| 9 | 4 | 5 |
| 10 | 1 | 3 |
+------+-----------+-------+
查找进球数超过25,体重小于50的球员信息:

select info.id,info.name,info.weight,sum(game.count) as total from (game inner join info on game.person_id = info.id) group by info.name where total > 25 and info.weight < 50;

select info.id,info.name,info.weight,sum(game.count) > 25 as total from (game inner join info on game.person_id = info.id) where info.weight < 50;

select *,info.weight,sum(game.count) as total from (game join info on game.person_id = info.id and info.weight < 50) group by info.id having sum(game.count) > 25;

select info.id,info.name,info.weight,sum(game.count) as total from (game inner join info on game.person_id = info.id) where info.weight < 50  group by info.name having sum(game.count) > 25;

查看答案及解析