sql - 在SQLite中滥用聚合

Question

此SQLite查询中发生此错误：

select f.rid, c.title, sum(some_expression) as ratio
from doesntmatter
where c.active = 1 or (ratio = 1.0 and c.active = 0
      and c.deactivated in (1, 2, 3, 4, 5)
group by f.rid

This问题使用 having子句解决了这个问题：

select f.rid, c.title, sum(some_expression) as ratio
from doesntmatter
where c.active = 1 or (c.active = 0
      and c.deactivated in (1, 2, 3, 4, 5)
group by f.rid
having ratio = 1.0

但就我而言，这改变了表达式的语义。您知道如何实现原始 where子句中的表达式吗？

Answer 1

基本上，您不能在where子句中引用别名列。查看此question，以了解如何解决该问题。

另外，您应按select语句中出现的所有非聚合列（在这种情况下为c.id和c.title）添加到组中。

很难为您重写该查询，因为其逻辑有点难以理解（由于错误的分组依据）。

编辑：

再三考虑之后，您可能只需要更正group by。

select c.id, c.title, sum(some_expression) as ratio
from doesntmatter
where c.active = 1 or (c.active = 0 and c.deactivated in (1, 2, 3, 4, 5))
group by c.id, c.title
having ratio = 1.0

或许：

select f.rid, sum(some_expression) as ratio
from doesntmatter
where c.active = 1 or (c.active = 0 and c.deactivated in (1, 2, 3, 4, 5))
group by f.rid
having ratio = 1.0

在上一部分中，我无能为力，因为我不知道表的字段，表数据，也不知道如何解释它们。但是选择分组依据中不存在的字段是一个坏主意。无论如何，请记住，您始终可以重新加入原始表并获得所需的信息，而不仅仅是 select中显示的字段。