MySQL 使用自定义 where 子句查看子查询？

Question

我正在尝试在存储有关足球(足球)信息的 MySQL 数据库上设计一个 View 。

我的目标是创建一个返回所有进球和得分者基本信息的 View 。这是 View 过去的简化版本:

SELECT P.Name AS Player,
       P.TeamName AS Team,
       (SELECT COUNT(*)
        FROM Goals G
        WHERE G.Scorer = P.playerID) AS TotalGoals
FROM Players P

到目前为止，一切正常。当我只想计算 WHERE 子句中指定的特定赛季内的进球数时，问题就出现了，如下所示:

SELECT * FROM GoalsView WHERE Season = 1

并且它只自动返回 WHERE 子句中本赛季的进球数，就好像子查询被自动编辑以使其看起来像这样:

[...]
(SELECT COUNT(*)
FROM Goals G
WHERE G.Scorer = P.playerID
AND Season=1) AS TotalGoals
[...]

我希望我已经说清楚了。感谢您的帮助!

Answer 1

一种可能的解决方案是

SELECT P.Name AS Player,
   P.TeamName AS Team,
   (SELECT COUNT(*)
    FROM Goals G
    WHERE G.Scorer = P.playerID AND season=1 GROUP By season) AS TotalGoals
 FROM Players P

我不知道你是否有单独的季节表或列