我不能写postgres查询。我总是得到一个错误或错误的结果。我试着比较今天和昨天的计数。
这个 MySQL 查询哪个工作正常：
SELECT
DATE_FORMAT(crh.date, '%d-%m-%Y') AS name,
DATE_FORMAT(crh.date, '%Y-%m-%d') AS nameGroup,
COUNT(crh.id) AS turnover,
crh_.name AS nameChr,
crh_.nameGroup AS nameGroupChr,
crh_.turnover AS turnoverChr
FROM
camera_reports_history AS crh
LEFT JOIN(
SELECT DATE_FORMAT(crh_.date, '%d-%m-%Y') AS name,
DATE_FORMAT(crh_.date, '%Y-%m-%d') AS nameGroup,
COUNT(crh_.id) AS turnover
FROM
camera_reports_history AS crh_
WHERE
crh_.date >= '2018-07-09 00:00:00' AND crh_.date <= '2018-07-20 14:02:22'
GROUP BY
nameGroup
) AS crh_
ON
crh_.nameGroup = DATE_FORMAT(SUBDATE(crh.date, 1),
'%Y-%m-%d')
WHERE
crh.date >= '2018-07-10 00:00:00' AND crh.date <= '2018-07-20 14:02:22'
GROUP BY
nameGroup结果：
"10-07-2018","2018-07-10","418","09-07-2018","2018-07-09","581"
"11-07-2018","2018-07-11","389","10-07-2018","2018-07-10","418"
"12-07-2018","2018-07-12","453","11-07-2018","2018-07-11","389"
"13-07-2018","2018-07-13","401","12-07-2018","2018-07-12","453"
...我的 PostgreSQL 查询如下所示：
SELECT
to_char(crh."date", 'DD-MM-YYYY') AS name,
to_char(crh."date", 'YYYY-MM-DD') AS nameGroup,
COUNT(crh.id) AS turnover,
crh_.name AS nameChr,
crh_.nameGroup AS nameGroupChr,
crh_.turnover AS turnoverChr
FROM
camera_reports_history AS crh
LEFT JOIN(
SELECT to_char(crh_."date", 'DD-MM-YYYY') AS name,
to_char(crh_."date", 'YYYY-MM-DD') AS nameGroup,
COUNT(crh_.id) AS turnover
FROM
camera_reports_history AS crh_
WHERE
crh_.date >= '2018-07-09 00:00:00' AND crh_.date <= '2018-07-20 14:02:22'
GROUP BY
nameGroup,
name
ORDER BY
nameGroup
) AS crh_
ON
crh_.nameGroup = to_char(
crh."date" - INTERVAL '1 day',
'YYYY-MM-DD'
)
WHERE
crh.date >= '2018-07-10 00:00:00' AND crh.date <= '2018-07-20 14:02:22'
GROUP BY
nameGroup,
name
ORDER BY
nameGroup错误：
ERROR: column `crh.date` must appear in the GROUP BY clause or be used in an aggregate function如果我插入必要的列：
GROUP BY nameGroup, name, date, crh_.nameGroup, crh_.name, crh_.turnover我会得到无用的结果。有人能帮帮我吗？
2条答案
按热度按时间bgibtngc1#
事务块在postgresql中工作得很好，因此您可以使用临时表，而不是试图一次对所有事务进行排序、联接、分组和筛选的复杂复合语句。这允许您将步骤分解为多个简单语句，并将中间数据存储在临时表中，临时表在事务块末尾消失。
我想你会发现这个方法更容易调试。
2ul0zpep2#
我找到了解决办法：
我做了第二个
LEFT JOIN计算结果并使用DISTINCT ON(nameGroup)总的来说SELECT.我认为这不是一个完美的查询。但现在我的问题解决了。请随时优化此查询。