SQL查询无法显示Key与Summary表数据，求语句修改方案

现有SQL查询可统计G1、G2类项目的数量，但Key和Summary字段无数据展示。这两个字段包含看板项目的关键信息，以下是原查询语句及修改尝试，均无法解决问题，请求修改SQL以显示这两个字段的数据。

原查询语句 #

SELECT COUNT(*) AS 'Projekte', T1.'Milestone', T1.'Key', T1.'Summary'
FROM 
(SELECT
      CASE 
          WHEN T1.'Labels' LIKE "%G1%" THEN "G1"
          WHEN T1.'Labels' LIKE "%G2%" THEN "G2" 
      END AS Milestone
FROM T*) T
WHERE T.Milestone IS NOT NULL
GROUP BY T.Milestone

修改尝试语句 #

SELECT COUNT(*) AS 'Projects', T1.'Milestone', T1.'Key', T1.'Summary'
FROM 
(SELECT
      CASE 
          WHEN T1.'Labels' LIKE "%G1%" THEN "G1"
          WHEN T1.'Labels' LIKE "%G2%" THEN "G2" 
      END AS Milestone
FROM T*) T
WHERE T.Milestone IS NOT NULL AND T1.Key IS NOT NULL AND T1.Summary IS NOT NULL
GROUP BY T.Milestone

问题核心是子查询仅返回Milestone字段，外层无法获取Key和Summary，且分组逻辑与展示明细的需求冲突，以下是两种可行修正方案：

方案1：同时展示分类统计数与项目明细 #

如果需要同时查看G1/G2的总数量和对应每个项目的Key、Summary，可使用窗口函数实现：

SELECT 
    COUNT(*) OVER(PARTITION BY Milestone) AS 'Projekte',
    Milestone,
    `Key`,
    `Summary`
FROM (
    SELECT
        CASE 
            WHEN `Labels` LIKE "%G1%" THEN "G1"
            WHEN `Labels` LIKE "%G2%" THEN "G2" 
        END AS Milestone,
        `Key`,
        `Summary`
    FROM T*
) T
WHERE T.Milestone IS NOT NULL
  AND T.`Key` IS NOT NULL 
  AND T.`Summary` IS NOT NULL

方案2：按分类分组并聚合明细 #

如果要按G1/G2分组，同时展示每组的Key和Summary列表（以MySQL为例，其他数据库需替换聚合函数）：

SELECT 
    COUNT(*) AS 'Projekte',
    Milestone,
    GROUP_CONCAT(DISTINCT `Key`) AS 'Keys',
    GROUP_CONCAT(DISTINCT `Summary`) AS 'Summaries'
FROM (
    SELECT
        CASE 
            WHEN `Labels` LIKE "%G1%" THEN "G1"
            WHEN `Labels` LIKE "%G2%" THEN "G2" 
        END AS Milestone,
        `Key`,
        `Summary`
    FROM T*
) T
WHERE T.Milestone IS NOT NULL
  AND T.`Key` IS NOT NULL 
  AND T.`Summary` IS NOT NULL
GROUP BY T.Milestone

关键修正说明 #

• 子查询必须包含Key和Summary字段，外层才能正常引用
• 原查询中外层直接使用T1属于错误引用，外层表别名是T，需以T为前缀访问字段
• 字段名建议用反引号`包裹，避免与SQL关键字冲突，不要用单引号

内容的提问来源于stack exchange，提问作者Jovan Aks