如何在没有“非分组字段x”的“used”上使用“HAVING”

Question

因此，我有这个查询，它在HeidiSQL中运行良好，但由于严格的模式(更确切地说可能是因为设置了ONLY_FULL_GROUP_BY )，在Laravel中没有运行：

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

它给出了以下错误：

语法错误或访问冲突:在hours子句中使用1463非分组字段“总报告时数”。

我已经尝试过在这里搜索，但是我发现的所有问题都通过将过滤器从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

我不知道MySQL MariaDB 10.2是基于哪个版本的，但至少在MySQL中，这是一个bug。它已在MySQL 5.7.5中得到修正。

• https://bugs.mysql.com/bug.php?id=51058
• 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