PostgreSql Group By 和聚合函数错误

Question

我的问题是，当我在 MySQL 中运行以下查询时，它看起来像这样

查询；

SELECT
CONCAT(b.tarih, '#', CONCAT(b.enlem, ',', b.boylam), '#', b.aldigi_yol) AS IlkMesaiEnlemBoylamImei,
CONCAT(tson.max_tarih, '#', CONCAT(tson.max_enlem, ',', tson.max_boylam), '#', tson.max_aldigi_yol) AS SonMesaiEnlemBoylamImei,
Max(CAST(b.hiz  AS UNSIGNED)) As EnYuksekHiz,
TIME_FORMAT(Sec_TO_TIME(TIMESTAMPDIFF(SECOND, (b.tarih), (tson.max_tarih))), '%H:%i') AS DurmaSuresi
FROM
(Select id as max_id, tarih as max_tarih, enlem as max_enlem, boylam as max_boylam, aldigi_yol as max_aldigi_yol from _213gl2015016424 where id in(
SELECT MAX(id)

FROM _213gl2015016424 where (tarih between DATE('2016-11-30 05:45:00') AND Date('2017-01-13 14:19:06')) AND CAST(hiz  AS UNSIGNED) > 0
GROUP BY DATE(tarih))
) tson
LEFT JOIN _213gl2015016424 a ON a.id = tson.max_id
LEFT JOIN _213gl2015016424 b ON DATE(b.tarih) = DATE(a.tarih)
WHERE b.tarih is not null And (b.tarih between DATE('2016-11-30 05:45:00') AND Date('2017-01-13 14:19:06')) AND b.hiz > 0
GROUP BY tson.max_tarih

输出按日期排序； Result query

当我尝试在 PostgreSQL 中运行查询时，我错误地得到了分组。

查询；

SELECT
    CONCAT(b.tarih, '#', CONCAT(b.enlem, ',', b.boylam), '#', b.toplamyol) AS IlkMesaiEnlemBoylamImei,
   CONCAT(tson.max_tarih, '#', CONCAT(tson.max_enlem, ',', tson.max_boylam), '#', tson.max_toplamyol) AS SonMesaiEnlemBoylamImei,
   Max(CAST(b.hiz  AS OID)) As EnYuksekHiz,
   to_char(to_timestamp((extract(epoch from (tson.max_tarih)) - extract(epoch from (b.tarih)))) - interval '2 hour','HH24:MI') AS DurmaSuresi
   FROM
   (Select id as max_id, tarih as max_tarih, enlem as max_enlem, boylam as max_boylam, toplamyol as max_toplamyol from _213GL2016008691 where id in(
       SELECT MAX(id)
        FROM _213GL2016008691 where (tarih between DATE('2018-02-01 03:31:54') AND DATE('2018-03-01 03:31:54')) AND CAST(hiz  AS OID) > 0
        GROUP BY DATE(tarih))
        ) tson
    LEFT JOIN _213GL2016008691 a ON a.id = tson.max_id
    LEFT JOIN _213GL2016008691 b ON DATE(b.tarih) = DATE(a.tarih)
    WHERE b.tarih is not null And (b.tarih between DATE('2018-02-12 03:31:54') AND DATE('2018-02-13 03:31:54')) AND b.hiz > 0
    GROUP BY tson.max_tarih

Group by 错误是:要使用聚合函数，您必须将列“b.tarih”添加到 GROUP BY 列表中。当我添加它时，另一列出现相同的错误。我正在等待您的帮助。

Answer 1

您正在使用不是标准 SQL 的 MySQL 功能，您也可以停用。您在查询中按 tson.max_tarih 分组。这意味着对于在该字段中共享相同值的所有行，作为该组的结果，您只会得到一行。

如果您在其余字段(enlem、boylam 等)中有几个不同的值，您希望将哪个值作为查询结果输入？这就是 PostgreSQL 向您提出的问题。

MySQL 只是返回组中行中那些字段的任何值。 PostgreSQL 要求您实际指定它。

两个典型的解决方案是按其余字段(b.tarih、b.enlem)分组或将这些字段的值指定为 MAX(b.tarih) 等。