mysql - MYSQL 查询中 GROUP_CONCAT 的替代方案

Question

有人可以告诉我如何更改我的查询，使其不使用GROUP_CONCAT。该查询非常适合 MYSQL 和 PHP，但我需要在 Filemaker Pro 中运行相同的查询，但 Filemaker 不支持 GROUP CONCAT。

SELECT sqlvalue
CAST(GROUP_CONCAT(IF(stat_number = 693, sqlvalue, NULL)) AS UNSIGNED) AS S693,
CAST(GROUP_CONCAT(IF(stat_number = 698, sqlvalue, NULL)) AS UNSIGNED) AS S698,
CAST(GROUP_CONCAT(IF(stat_number = 2010, sqlvalue, NULL)) AS UNSIGNED) AS S2010,
CAST(GROUP_CONCAT(IF(stat_number = 2020, sqlvalue, NULL)) AS UNSIGNED) AS S2020,
IFNULL(CAST(GROUP_CONCAT(IF(stat_number = 693, sqlvalue, NULL)) AS UNSIGNED),0) +
IFNULL(CAST(GROUP_CONCAT(IF(stat_number = 698, sqlvalue, NULL)) AS UNSIGNED),0) +
IFNULL(CAST(GROUP_CONCAT(IF(stat_number = 2010, sqlvalue, NULL)) AS UNSIGNED),0) +
IFNULL(CAST(GROUP_CONCAT(IF(stat_number = 2020, sqlvalue, NULL)) AS UNSIGNED),0) + AS STOTAL
FROM statsvalue
WHERE sqldate > ’01-07-2013′
ORDER BY sqldate ASC
GROUP By sqldate

字段 stat_number 是为输入的记录指定的代码，sqlvalue 是数值，sqldate 是日期。

上面的查询获取这些stat_number的值，并将它们加在一起，并按sqldate字段对总数进行分组。

Answer 1

也许我遗漏了一些东西，但在这种特殊情况下(进行条件聚合)你不需要GROUP_CONCAT()。

因此，恕我直言，您可以继续将 GROUP_CONCAT() 更改为 MAX()，这可能更有意义，至少对于读取查询而言。

我不了解 Filemaker，但您也可以考虑更改

1. IF() 到 CASE END
2. IFNULL() 到 COALESCE()

此外，您的查询还有一些其他问题:

1. 即使 MySQL 允许，您也不应该在 SELECT 子句中使用 sqlvalue 列，而不应用聚合函数使其成为 group by 的一部分。这是没有意义的，其他主要的 RDBMS(SQL Server、Oracle、Postgres)也不允许这样做。 您很可能想使用sqldate。
2. 即使您将 sqlvalue 放入 SELECT 中，列名后面也应该使用逗号
3. AS STOTAL 之前有一个额外的 +
4. 在使用日期字符串文字时，您可能需要使用正确的引号而不是智能引号
5. 您最好使用消除歧义的日期文字(您应该使用“2013-07-01”或“2013-01-07”，而不是“01-07-2013”​​，具体取决于您的意思)
6. ORDER BY 应该位于 GROUP BY
之后

话虽如此，您的查询可能看起来像

SELECT sqldate,
       CAST(MAX(CASE WHEN stat_number = 693  THEN sqlvalue END) AS UNSIGNED) AS S693,
       CAST(MAX(CASE WHEN stat_number = 698  THEN sqlvalue END) AS UNSIGNED) AS S698,
       CAST(MAX(CASE WHEN stat_number = 2010 THEN sqlvalue END) AS UNSIGNED) AS S2010,
       CAST(MAX(CASE WHEN stat_number = 2020 THEN sqlvalue END) AS UNSIGNED) AS S2020,
       COALESCE(MAX(CASE WHEN stat_number = 693  THEN sqlvalue END) AS UNSIGNED), 0) +
       COALESCE(MAX(CASE WHEN stat_number = 698  THEN sqlvalue END) AS UNSIGNED), 0) +
       COALESCE(MAX(CASE WHEN stat_number = 2010 THEN sqlvalue END) AS UNSIGNED), 0) +
       COALESCE(MAX(CASE WHEN stat_number = 2020 THEN sqlvalue END) AS UNSIGNED), 0) AS STOTAL
  FROM statsvalue
 WHERE sqldate > '2013-01-07'
 GROUP BY sqldate
 ORDER BY sqldate

或者使用像这样的内联 View

SELECT sqldate, S693, S698, S2010, S2020,
       COALESCE(S693,  0) + 
       COALESCE(S698,  0) + 
       COALESCE(S2010, 0) + 
       COALESCE(S2020, 0) AS STOTAL
  FROM
(
  SELECT sqldate
         CAST(MAX(CASE WHEN stat_number = 693  THEN sqlvalue END) AS UNSIGNED) AS S693,
         CAST(MAX(CASE WHEN stat_number = 698  THEN sqlvalue END) AS UNSIGNED) AS S698,
         CAST(MAX(CASE WHEN stat_number = 2010 THEN sqlvalue END) AS UNSIGNED) AS S2010,
         CAST(MAX(CASE WHEN stat_number = 2020 THEN sqlvalue END) AS UNSIGNED) AS S2020
    FROM statsvalue
   WHERE sqldate > '2013-01-07'
   GROUP BY sqldate
) q
  ORDER BY sqldate