我有一个不同球队之间的比赛表，如下所示，我的目标是编写一个sql查询，以获得每个队在每个位置的最新比赛日期。
桌上锦标赛快照：
代码语言：javascript复制Match Date Match_Location Team_a Team_b Team_a_Goals Team_b_Goals
2020-01-01 Chennai CSK RCB 2 4
2020-01-01 Mumbai Kolkata CSK 4 6
2020-12-31 Bangalore Daredevils Kolkata 1 1
2020-05-03 Kolkata RCB CSK 3 2
2020-06-15 Indore Kolkata RCB 1 0了解每支球队在每个地点的比赛日期。预期结果：
代码语言：javascript复制recent_match_date, recent_location, team
2020-05-03 Kolkata CSK
2020-06-15 Indore Kolkata
2020-12-31 Bangalore Daredevils
2020-06-15 Indore RCB以抽签结束的每个地点的匹配总数。列出所有位置。预期结果：
代码语言：javascript复制location n_matches_draw
Kolkata 0
Indore 0
Bangalore 1
Indore 0在每一个地点得到得分最高的球队和总目标。我第一次查询的方法是使用Union，但它不是最佳解决方案，因为我使用的是Union和子查询。
代码语言：javascript复制select team, location , max(match_date) from (
select team_a as team, match_location as location, min(match_date) as match_date
from tournament group by team_a, match_location
union
select team_b as team, match_location as location, max(match_date) as match_date
from tournament group by team_b, match_location
) group by team, match_location对于第二个问题(每个位置的抽签总数)，以下是我的查询：
代码语言：javascript复制select location, count(*) as draw_count
from (select * from tournament where team_a_goals=team_b_goals)
group by location对于第三个查询，我有使用Union的方法。但是，我不清楚我的查询是否解决了所要求的内容。
代码语言：javascript复制select location, case when team_a_goals > team_b_goals then team_a_goals as goals, team_a as team
union
select location, case when team_b_goals > team_a_goals then team_b_goals as goals, team_b as team