mysql - 合并两个没有公共(public)字段的 COUNT 总计

Question

我正在寻找一些有关如何使用每个表中不具有公共(public)字段名称的字段来组合来自不同表的两个 COUNT 总数的最佳方法的建议。

目前我有两个单独的查询，它们能够计算出现次数和替换次数，如下所示。

出场

SELECT
  P.PlayerID AS id, COUNT(A.AppearancePlayerID) AS apps
FROM 
  sports_seasons S
LEFT OUTER JOIN sports_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$seasonid'
LEFT OUTER JOIN sports_appearances A ON A.AppearancePlayerID = S.SeasonPlayerID AND A.AppearanceSeasonID = '$seasonid'
WHERE 
  P.PlayerID != ''
GROUP BY id
ORDER BY id

替换

SELECT
   P.PlayerID AS id, COUNT(SU.SubstitutionPlayerIDIn) AS subapps
FROM 
   sports_seasons S
LEFT OUTER JOIN sports_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$seasonid'
LEFT OUTER JOIN sports_substitutions SU ON SU.SubstitutionPlayerIDIn = S.SeasonPlayerID AND SU.SubstitutionSeasonID = '$seasonid'
WHERE 
  P.PlayerID != ''
GROUP BY id
ORDER BY id

我目前的尝试如下:

SELECT P.PlayerID AS id,    
  (
  SELECT
    COUNT(A.AppearancePlayerID) AS apps
  FROM 
    sports_seasons S
  LEFT OUTER JOIN sports_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$seasonid'
  LEFT OUTER JOIN sports_appearances A ON A.AppearancePlayerID = S.SeasonPlayerID AND A.AppearanceSeasonID = '$seasonid'
  WHERE 
    P.PlayerID != '' AND P.PlayerPublish != 0
  )
  +
  ( 
  SELECT
    COUNT(SU.SubstitutionPlayerIDIn) AS subapps
  FROM 
    sports_seasons S
  LEFT OUTER JOIN sports_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$seasonid'
  LEFT OUTER JOIN sports_substitutions SU ON   SU.SubstitutionPlayerIDIn = S.SeasonPlayerID AND SU.SubstitutionSeasonID = '$seasonid'
  WHERE 
    P.PlayerID != '' AND P.PlayerPublish != 0               
  ) AS total
FROM sports_players P
GROUP BY id
ORDER BY id

可悲的是，我无处可去。

下面是正在使用的表格。 DB 是运动队球员出场的记录。

PlayerID | PlayerName // Players table

AppearanceID | AppearancePlayerID | AppearanceSeasonID // A table collating first team appearance made

SubstitutionID | SubstitutionPlayerIDIn | SubstitutionSeasonID // A table collating substitution appearances made

SeasonID | SeasonPlayerID // A table handling registrations

使用 MySQL 5.6。

Answer 1

您可以使用两个查询的结果作为派生表来连接它们。我认为 INNER JOIN 就足够了。

SELECT a.id as id, apps + subapps as total
FROM 
(
SELECT
  P.PlayerID AS id, COUNT(A.AppearancePlayerID) AS apps
FROM 
  sports_seasons S
LEFT OUTER JOIN sports_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$seasonid'
LEFT OUTER JOIN sports_appearances A ON A.AppearancePlayerID = S.SeasonPlayerID AND A.AppearanceSeasonID = '$seasonid'
WHERE 
  P.PlayerID != ''
GROUP BY P.PlayerID
) as a 
INNER JOIN 
(
SELECT
   P.PlayerID AS id, COUNT(SU.SubstitutionPlayerIDIn) AS subapps
FROM 
   sports_seasons S
LEFT OUTER JOIN sports_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$seasonid'
LEFT OUTER JOIN sports_substitutions SU ON SU.SubstitutionPlayerIDIn = S.SeasonPlayerID AND SU.SubstitutionSeasonID = '$seasonid'
WHERE 
  P.PlayerID != ''
GROUP BY P.PlayerID
) as b on a.id = b.id
ORDER BY a.id;