mysql - 创建求和的 View ，选择子表的最小值，然后选择子表的最大值

我有3张桌子，我想加入一个视图

佣金表：

idCommission: int
name:         varchar
...

季节：

idSeason:     int
since:        date
till:         date

Seasons_in_commission：

idCommission: int
idSeason:     int

结果视图：

idCommission: int    | ID for referencing
Commission.*         | All other from Commission table
since:        date   | Min of all its (from) seasons
till:         date   | Max of all its (till) seasons
seasons:      int    | Count of its seasons

我已经尝试过，但是我发现，我需要在子查询中逐行引用我不知道该怎么做。

SELECT Commission.*,
MIN(
  SELECT since FROM Seasons_in_commission
  JOIN Season USING(idSeason)
  WHERE idCommission = currentRow.idCommission /*<- this part is wrong */
) as Since, MAX(...) as Till, COUNT(...) as Seasons FROM Commission

最佳答案

我认为您不需要任何子查询。尝试这个：

SELECT Commission.*, MIN(since) AS Since, MAX(till) AS Till, COUNT(*) AS Seasons
  FROM Commission
  JOIN Seasons_in_commission USING (idCommission)
  JOIN Season USING (idSeason)
 GROUP BY idCommission;

（请注意，这仅在禁用ONLY_FULL_GROUP_BY的情况下有效。）