子查询组（按不同列）

Question

我有一个查询的简化版本，如下所示:

SELECT count_last_8, goals_8, count_last_16, goals_16 FROM (
            
                (SELECT COUNT(*) AS count_last_8,  f_total_ftg AS goals_8
                            FROM (SELECT * FROM results ORDER BY f_datetime LIMIT 8 ) AS X ) last_8g,
                                
                 (SELECT COUNT(*) AS count_last_16, f_total_ftg AS goals_16
                            FROM (SELECT * FROM results ORDER BY f_datetime LIMIT 16 ) AS X ) last_16g
                        )

在Results表中还有两列-F_Player1和F_Player2，我希望按Distincent播放器对查询进行分组

所以玩家的名单是:

SELECT DISTINCT f_player1 AS player FROM results UNION SELECT DISTINCT f_player2 AS player FROM results

如何添加此条件以使每个表别名(last8g，last16g)适用于这些不同的播放器？

具有当前查询的示例数据:

count_last_8    |   goals_8    | count_last_16   |  goals_16
    8                 4                16              4

我想要的是这样的:

  player  |   count_last_8    |   goals_8    | count_last_16   |  goals_16
    kray           2                 2                4              9
    peli           1                 3                3              7
    etc...

Answer 1

这是相当难理解你的问题，如果有一个真实的数据样本，也有一个真实的基于提供的样本的预期结果，这会容易得多。

但是，我尝试了一些东西，它可能是解决办法，也可能会帮助你:

SELECT 
  T1.player, 
  T1.count_last_8, 
  T1.goals_8, 
  T2.count_last_16, 
  T2.goals_16
FROM(
  SELECT 
    f_player1 AS player, 
    SUM(c1) AS count_last_8, 
    SUM(c2) AS goals_8
  FROM(
    SELECT 
      f_player1, 
      COUNT(f_player1) AS c1, 
      SUM(f_total_ftg) AS c2 
    FROM(
      SELECT * 
      FROM 
        results
      ORDER BY f_datetime
      LIMIT 8
    ) ST1
    GROUP BY f_player1
    UNION ALL
    SELECT 
      f_player2, 
      COUNT(f_player2) AS c1, 
      SUM(f_total_ftg) AS c2 
    FROM(
      SELECT * 
      FROM 
        results
      ORDER BY f_datetime
      LIMIT 8
    ) ST2
    GROUP BY f_player2 
  ) ST3
  GROUP BY f_player1
) T1
JOIN
(
  SELECT 
    f_player1 AS player, 
    SUM(c1) AS count_last_16, 
    SUM(c2) AS goals_16
  FROM(
    SELECT f_player1, 
    COUNT(f_player1) AS c1, 
    SUM(f_total_ftg) AS c2 
    FROM(
      SELECT * 
      FROM 
        results
      ORDER BY f_datetime
      LIMIT 16
    ) ST4
    GROUP BY f_player1
    UNION ALL
    SELECT 
      f_player2, 
      COUNT(f_player2) AS c1, 
      SUM(f_total_ftg) AS c2 
    FROM(
      SELECT * 
      FROM 
        results
      ORDER BY f_datetime
      LIMIT 16
    ) ST5
    GROUP BY f_player2
  ) ST6
  GROUP BY f_player1
) T2
ON T1.player = T2.player

请参阅此处的演示