如何在没有"Non-grouping field x is used in HAVING clause"的情况下"拥有""SUM"

Question

所以，我有这个查询，它在HeidiSQL中运行良好，但在Laravel中运行不好，因为严格模式(更具体地说，可能是因为设置了SQL模式ONLY_FULL_GROUP_BY)：

SELECT
project.id
'Project code',
SUM(report.hours)
'Total hours',
SUM(CASE WHEN report.date BETWEEN :report_start AND :report_end THEN report.hours END)
'Total report hours'
FROM report
INNER JOIN project ON project.id = report.id
WHERE report.date <= :report_end
GROUP BY
project.id
HAVING
`Total report hours` != 0

它给了我以下错误：

语法错误或访问冲突：1463 HAVING子句中使用了非分组字段"Total report hours">

我试过在这里搜索，但我发现的所有问题都可以通过将过滤器从HAVING移动到WHERE来解决，这是可以做到的，因为这些情况实际上并不包括聚合函数。然而，在我的情况下，确实如此，我认为这正是HAVING的用途？即，我想过滤掉所有在报告期间内没有任何小时的行。

我可以"解决"它，而不是引用Total report hours，而是重复整个SUM，如下所示：

HAVING
SUM(CASE WHEN report.date BETWEEN :report_start AND :report_end THEN report.hours END) != 0

但是，当它已经计算好并且(从我的角度来看)在查询本身中可用时，这感觉非常不必要和混乱。

那么，有没有一种方法可以表达这一点，在严格模式下工作，而没有来重复自己？

Answer 1

我不知道MariaDB 10.2是基于哪个版本的MySQL，但至少在MySQL中，这是一个错误。它已经在MySQL 5.7.5中修复。

• https://bugs.mysql.com/bug.php?id=51058
• http://mysqlserverteam.com/mysql-5-7-only_full_group_by-improved-recognizing-functional-dependencies-enabled-by-default/

MySQL手册甚至提到了这一点：

从MySQL 5.7.5开始，默认的SQL模式包括ONLY_FULL_GROUP _BY。(在5.7.5之前，MySQL不检测功能依赖，默认情况下ONLY_FULL_GROUP_BY未启用。

Answer 2

在一些数据库中，您需要复制聚合表达式：

SELECT p.id as 'Project code',
SUM(r.hours) as 'Total hours',
SUM(CASE WHEN r.date BETWEEN :report_start AND :report_end THEN r.hours END) as 'Total report hours'
FROM report r INNER JOIN
project p
ON p.id = r.id
WHERE r.date <= :report_end
GROUP BY p.id
HAVING SUM(CASE WHEN r.date BETWEEN :report_start AND :report_end THEN r.hours END) <> 0;

我建议您不要对列别名使用单引号。您使用的是多个数据库，所以不清楚哪种转义符最好——反引号、双引号或方括号。

当然，您也可以使用子查询。

Answer 3

将查询封装在派生表中。添加顶级条件：

select *
from
(
SELECT
project.id
'Project code',
SUM(report.hours)
'Total hours',
SUM(CASE WHEN report.date BETWEEN :report_start AND :report_end THEN report.hours END)
'Total report hours'
FROM report
INNER JOIN project ON project.id = report.id
WHERE report.date <= :report_end
GROUP BY
project.id
) dt
WHERE `Total report hours` != 0