请有人告诉我如何更改我的查询,使其不使用GROUP_CONCAT。这个查询对于MYSQL和PHP来说很好,但是我需要在Filemaker Pro中运行相同的查询,但是Filemaker不支持组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 field对总数进行分组。
发布于 2013-11-18 05:43:07
也许我遗漏了一些东西,但在这种特殊情况下,您不需要GROUP_CONCAT() (执行条件聚合)。
因此,您可以继续使用MAX()更改MAX(),这可能更有意义,至少对于读取查询来说是这样。
我不知道菲梅克,但你也可以考虑改变
IF()到CASE ENDIFNULL()到COALESCE()此外,您的查询还有其他一些问题:
SELECT子句中使用sqlvalue列,除非将聚合函数应用于group。这是没有意义的,其他主要的RDBMS ( Server、Oracle、Postgres)也不允许这样做。您最可能打算使用的是sqldate ,而不是.。sqlvalue放在SELECT中,也应该在列名后面使用逗号。+之前还有一个额外的AS STOTALORDER 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或者像这样使用内联视图
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 sqldatehttps://stackoverflow.com/questions/20039723
复制相似问题