MySQL 两表两列COUNT

Question

我有两张 table

TEAMS - team_id and team_name (among other stuff)
SCHEDULE - game_id, team_a, team_b, team_a_id, team_a_id (among other stuff)

我正在尝试创建一个结果，其中我找出 team_name(或 team_id)出现在任一列 team_a 中的次数或 team_b(或 team_a_id 或 team_b_id)

团队
team_id team_name
1001纽约
1011克利夫兰
1021底特律
1031休斯顿

时间表
game_id team_a team_b team_a_id team_b_id
1 纽约克利夫兰 1001 1011
2 纽约底特律 1001 1021
3 克利夫兰休斯顿 1011 1031

回答:纽约 2克利夫兰 2底特律 1休斯顿 1

Answer 1

单向

Select t.team_name,Sum(allteams.teamcount) as NumberOfTimes
From teams t
inner join(
select team_a_id as team_ID, 1 as teamcount From Schedule
Union 
Select team_b_id, 1 From Schedule
) allteams
on allteams.Team_ID = t.Team_ID
Group By t.team_name