mysql - 以案例和组为中心

Question

我有一个不优雅的解决方案，可以使用我之前使用过的 CASE 函数在 MySQL 中旋转 SELECT 查询，但我无法看到我可能会出错的地方，因为我没有得到我期望的结果。为了传达我的意图:

我正在尝试跟踪时间戳的进度以获得唯一的引用，我可以轻松地提取 SELECT 查询，但需要将它们转换为行。我已经通过以下查询完成了此操作:

SELECT c.ID, d.lead_id,
CASE WHEN s.summary LIKE '%submitted and status set to "Pending FA Approval"%' THEN DATE_FORMAT(CONVERT_TZ (s.created,'+00:00','-06:00'), '%M %e, %Y [%h:%i %p]') END AS a,
CASE WHEN s.summary LIKE '%("Pending FA Approval" => "Not Approved, Please Revise")%' THEN DATE_FORMAT(CONVERT_TZ (s.created,'+00:00','-06:00'), '%M %e, %Y [%h:%i %p]') END AS b,
CASE WHEN s.summary LIKE '%("Not Approved, Please Revise" => "Pending FA Approval")%' THEN DATE_FORMAT(CONVERT_TZ (s.created,'+00:00','-06:00'), '%M %e, %Y [%h:%i %p]') END AS c,
CASE WHEN s.summary LIKE '%("Pending FA Approval" => "Complete, Pending Payment")%' THEN DATE_FORMAT(CONVERT_TZ (s.created,'+00:00','-06:00'), '%M %e, %Y [%h:%i %p]') END AS d,
CASE WHEN s.summary LIKE '%("Complete, Pending Payment" => "Paid, Complete")%' THEN DATE_FORMAT(CONVERT_TZ (s.created,'+00:00','-06:00'), '%M %e, %Y [%h:%i %p]') END AS e,
CASE WHEN s.summary LIKE '%("Paid, Complete" => "Processing")%' THEN DATE_FORMAT(CONVERT_TZ (s.created,'+00:00','-06:00'), '%M %e, %Y [%h:%i %p]') END AS f,
CASE WHEN s.summary LIKE '%("Paid, Complete" => "Processed")%' THEN DATE_FORMAT(CONVERT_TZ (s.created,'+00:00','-06:00'), '%M %e, %Y [%h:%i %p]') END AS g
FROM lead_detail d
JOIN stream s ON s.object_id = d.lead_id
JOIN chapter c ON c.ID = d.`value`
WHERE d.field_number = 41
AND s.created >= "2017-02-15"
AND s.action = "status-change"
AND s.summary NOT LIKE "%Voided%"
AND c.ID = 549

这将返回以下内容:

 ID     |   lead_id |   a                           |   b       |   c       |   d                           |   e       |   f       |   g
 549    |   14512   |   February 15, 2017 [08:53 PM]|   NULL    |   NULL    |   NULL                        |   NULL    |   NULL    |   NULL
 549    |   14512   |   NULL                        |   NULL    |   NULL    |   February 15, 2017 [08:54 PM]|   NULL    |   NULL    |   NULL

我遇到的问题是，当我通过在查询末尾附加 GROUP BY c.ID 进行聚合时，我得到:

 ID     |   lead_id |   a                           |   b       |   c       |   d       |   e       |   f       |   g
 549    |   14512   |   February 15, 2017 [08:53 PM]|   NULL    |   NULL    |   NULL    |   NULL    |   NULL    |   NULL

而不是我想要的:

 ID |   lead_id |   a                           |   b       |   c       |   d                           |   e       |   f       |   g
549 |   14512   |   February 15, 2017 [08:53 PM]|   NULL    |   NULL    |   February 15, 2017 [08:54 PM]|   NULL    |   NULL    |   NULL

这是一个相对示例，而我正在使用的其他示例在整个 CASE 语句 a-g 中偶尔具有不同的值。我查看了许多来源，似乎 GROUP 聚合不能很好地与 NULL 放在一起，但我找不到一个很好的具体示例或解释来让我得到我需要的东西:一行，结果从单独列中的 CASE 语句，其中返回值为 NULL(或 '')，直到创建日期条目时被替换。这也需要在选择查询中完成，而不方便将数据重新组织到更适合的关系表中。

此外，如果重要的话，需要按照说明解决此示例，但要知道它也将通过 UNION 连接到其他 c.ID 值。

任何帮助或指导将不胜感激，即使只是指出我对此完全错误。

Answer 1

单独将 GROUP c.ID 添加到当前查询中没有任何逻辑意义，甚至不会在大多数数据库中运行，因为一旦指定 GROUP BY >，您正在告诉数据库聚合组记录。因此，您选择的每一列必须是组本身(即可以选择 c.ID)或必须涉及某些聚合函数，例如 MAX( ) 或 SUM()。

SELECT c.ID,
       d.lead_id,
       MAX(CASE WHEN s.summary LIKE '%submitted and status set to "Pending FA Approval"%'
                THEN DATE_FORMAT(CONVERT_TZ (s.created,'+00:00','-06:00'), '%M %e, %Y [%h:%i %p]') END) AS a,
       MAX(CASE WHEN s.summary LIKE '%("Pending FA Approval" => "Not Approved, Please Revise")%'
                THEN DATE_FORMAT(CONVERT_TZ (s.created,'+00:00','-06:00'), '%M %e, %Y [%h:%i %p]') END) AS b,
       MAX(CASE WHEN s.summary LIKE '%("Not Approved, Please Revise" => "Pending FA Approval")%'
                THEN DATE_FORMAT(CONVERT_TZ (s.created,'+00:00','-06:00'), '%M %e, %Y [%h:%i %p]') END) AS c,
       MAX(CASE WHEN s.summary LIKE '%("Pending FA Approval" => "Complete, Pending Payment")%'
                THEN DATE_FORMAT(CONVERT_TZ (s.created,'+00:00','-06:00'), '%M %e, %Y [%h:%i %p]') END) AS d,
       MAX(CASE WHEN s.summary LIKE '%("Complete, Pending Payment" => "Paid, Complete")%'
                THEN DATE_FORMAT(CONVERT_TZ (s.created,'+00:00','-06:00'), '%M %e, %Y [%h:%i %p]') END) AS e,
       MAX(CASE WHEN s.summary LIKE '%("Paid, Complete" => "Processing")%'
                THEN DATE_FORMAT(CONVERT_TZ (s.created,'+00:00','-06:00'), '%M %e, %Y [%h:%i %p]') END) AS f,
       MAX(CASE WHEN s.summary LIKE '%("Paid, Complete" => "Processed")%'
                THEN DATE_FORMAT(CONVERT_TZ (s.created,'+00:00','-06:00'), '%M %e, %Y [%h:%i %p]') END) AS g
FROM lead_detail d
INNER JOIN stream s
    ON s.object_id = d.lead_id
INNER JOIN chapter c
    ON c.ID = d.`value`
WHERE d.field_number = 41           AND
      s.created >= "2017-02-15"     AND
      s.action = "status-change"    AND
      s.summary NOT LIKE "%Voided%" AND
      c.ID = 549
GROUP BY c.ID

这是一个简短的图表，显示 MAX() 如何使枢轴工作

id | col
1  | 2         <-- the max of col when id=1 is 2, because NULLs are ignored
1  | NULL
1  | NULL
2  | NULL
2  | 5         <-- the max of col when id=2 is 5, because NULLs are ignored
2  | NULL