将3个SQL SELECT合并为一个语句

Question

I have 3 tables :

• Teams (id_team, name, id_season)
• Seasons (id_season, name, nbr_teams)
• Teams_Stats (id_stats, id_game, id_team, victory, defeat, draw)

I have 3 queries that are working fine individually :

SELECT T.name, count(TS.victory) as Wins
FROM Teams T
JOIN Seasons S ON T.id_season = S.id_season AND S.name = '2015'
LEFT JOIN Teams_Stats TS ON TS.id_team = T.id_team AND TS.victory = 1
GROUP BY T.name 
ORDER BY T.name

SELECT T.name, count(TS.defeat) as Losses
FROM Teams T
JOIN Seasons S ON T.id_season = S.id_season AND S.name = '2015'
LEFT JOIN Teams_Stats TS ON TS.id_team = T.id_team AND TS.defeat = 1
GROUP BY T.name 
ORDER BY T.name

SELECT T.name, count(TS.victory) as Draws
FROM Teams T
JOIN Seasons S ON T.id_season = S.id_season AND S.name = '2015'
LEFT JOIN Teams_Stats TS ON TS.id_team = T.id_team AND TS.draw = 1
GROUP BY T.name 
ORDER BY T.name

I was wondering how can I can get the same result but within only 1 query. I just can't get it ? Maybe someone can throw some light ...

I appreciate,

Thanks.

Answer 1

You can use conditional aggregation:

SELECT T.name, 
       sum(TS.victory=1) as Wins,
       sum(TS.defeat=1) as Losses,
       sum(TS.draw =1) as Draws
FROM Teams T
JOIN Seasons S ON T.id_season = S.id_season AND S.name = '2015'
LEFT JOIN Teams_Stats TS ON TS.id_team = T.id_team 
GROUP BY T.name 
ORDER BY T.name

Demo here