mysql - MySQL 查询总数

Question

我有三张桌子

我已经执行了这个查询：

SELECT 
`tbl_players`.`id` AS `player_id`
,`tbl_players`.`player_name`
,`tbl_sports`.`sports_name`
,`tbl_scores`.`score`
FROM `tbl_players`
LEFT JOIN `tbl_scores` ON `tbl_players`.`id`=`tbl_scores`.`player_id`
LEFT JOIN `tbl_sports` ON `tbl_players`.`sports_id`=`tbl_sports`.`sports_id`
ORDER BY `tbl_players`.`id` ASC

我得到了这个：

现在我想要这个：

标签： mysqlsql

Answer 1

使用条件聚合

select player_id,player_name,sports_name,
       sum(case when season=1 then score end) as season1_score,
       sum(case when season=2 then score end) as season2_score,
       sum(case when season=3 then score end) as season3_score
from
(
SELECT 
`tbl_players`.`id` AS `player_id`
,`tbl_players`.`player_name`
,`tbl_sports`.`sports_name`
,`tbl_scores`.`score`,`tbl_scores`.`season`
FROM `tbl_players`
LEFT JOIN `tbl_scores` ON `tbl_players`.`id`=`tbl_scores`.`player_id`
LEFT JOIN `tbl_sports` ON `tbl_players`.`sports_id`=`tbl_sports`.`sports_id`
)A group by player_id,player_name,sports_name