我想計算兩支球隊之間的比賽次數。
select home.home_team, home.away_team, (home_matches_against away_matches_against) as matches
from
(select home_team, away_team, count(home_team) as home_matches_against
from results
group by home_team, away_team) as home
join
(select away_team, home_team, count(away_team) as away_matches_against
from results
group by away_team, home_team) as away
on home.home_team=away.away_team and home.away_team=away.home_team
輸出：
如您所見，兩隊之間的比賽次數是重復的。
我想要的結果是例如：
home team away _team matches
England Scotland 117
Wales Scotland 106
等等。我不想重復匹配的數量
uj5u.com熱心網友回復：
您可以添加WHERE子句：
WHERE home.home_team < home.away_team;
uj5u.com熱心網友回復：
您可以使用 LEAST 和 GREATEST。就像是：
SELECT t1.*
FROM ( SELECT LEAST(home_team, away_team) AS home_team,
GREATEST(home_team, away_team) AS away_team,
matches
FROM results
) AS t1
GROUP BY t1.home_team, t1.away_team,t1.matches;
https://www.db-fiddle.com/f/uLPPE1DoKjKYBoSXfKahJN/21
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/493859.html