我有一個查詢，顯示資料庫中各個球隊贏??得的比賽總數，
select t.name, count(*) 'Matches_Won'
from test.team t
inner join test.match_scores m on m.winner = t.id
group by t.name
order by Matches_Won desc;
另一個查詢列印出資料庫中各個球隊贏??得的比賽總數，
select t.name, count(*) 'Matches_PLayed'
from test.team t
inner join test.match_scores m on m.home_team = t.id or m.away_team = t.id
group by t.name
order by Matches_Played desc;
現在，我正在嘗試組合這兩個查詢，我想要一個包含三列的表，
- 隊名
- 打過的比賽
- 比賽獲勝
我試圖聯合這兩個查詢，但沒有奏效。任何人都可以指導我嗎？
**編輯： **
這是團隊表
這是比賽得分表。在該表中，“主隊”、“客隊”列代表各隊的進球數，“贏家”是外鍵，指的是球隊表。
uj5u.com熱心網友回復：
這應該作業
select t.name, (select count(*) from match_scores where winner = t.id) as 'Matches_Won', (select count(*) from match_scores where home_team = t.id or away_team=t.id) as 'Matches_PLayed'
from team t
uj5u.com熱心網友回復：
SELECT t.name,
SUM(m.winner = t.id) 'Matches_Won',
SUM(t.id IN (m.home_team, m.away_team)) 'Matches_Played'
FROM test.team t
CROSS JOIN test.match_scores m
GROUP BY t.name;
uj5u.com熱心網友回復：
你想加入子查詢，
select
w.name,
w.Matches_Won,
p.Matches_Played
from
(
select
t.id,
count(*) 'Matches_PLayed'
from
test.team t
inner join
test.match_scores m
on m.home_team = t.id or m.away_team = t.id
group by
t.id
) p
left join
(
select
t.id
t.name,
count(*) 'Matches_Won'
from
test.team t
inner join
test.match_scores m
on m.winner = t.id
group by
t.id
) w
on w.id = p.id
order by
w.Matches_Won,
p.Matches_Played
但是，也可以將這些聚合組合到一個查詢中......
select
t.name,
sum(case when m.winner = t.id then 1 else 0 end) 'Matches_Won'
count(*) 'Matches_Played'
from
test.team t
inner join
test.match_scores m
on m.home_team = t.id or m.away_team = t.id
group by
t.id
order by
Matches_Won,
Matches_Played
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/504977.html