用sql语句查询List every match with the goals scored by each team as shown. This will use "CASE WHEN" . If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.

以下是查询语句：

SELECT mdate, matchid, team1, team2, 
       SUM(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) AS score1,
       SUM(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) AS score2
FROM matches JOIN goals ON matches.id = goals.matchid
GROUP BY mdate, matchid, team1, team2
ORDER BY mdate, matchid, team1, team2;

这个查询语句使用了 "CASE WHEN" 语句，将每个进球分别统计到了对应的队伍的得分中，然后进行了分组和排序。你可以根据需要进行调整。

相关问题

用sql语句查询For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'

以下是查询语句：

SELECT matchid, date, COUNT(*) AS goals_scored
FROM match
JOIN goal ON match.id = goal.matchid
WHERE team1 = 'GER' AND goal.teamid = 'GER'
GROUP BY matchid, date;

这条 SQL 语句首先使用 JOIN 将 match 表和 goal 表连接，然后使用 WHERE 子句筛选出所有 team1 为 'GER' 且 goal 表中 teamid 为 'GER' 的比赛。最后使用 GROUP BY 对每场比赛的 matchid 和 date 进行分组，并使用 COUNT(*) 计算出 'GER' 这个队在每场比赛中进球的数量。

用sql语句查询Show the stadium and the number of goals scored in each stadium.

可以使用如下的 SQL 查询语句：

SELECT Matches.stadium, COUNT(Goals.matchid) AS num_goals
FROM Matches
INNER JOIN Goals ON Matches.id = Goals.matchid
GROUP BY Matches.stadium;

这个查询语句将会返回每个球场的名称以及在该球场上进球的数量。它使用了一个内连接将 Matches 和 Goals 两个表连接在一起，然后使用 GROUP BY 子句按照球场名称进行分组，并使用 COUNT 函数统计每个球场上进球的数量。