mysql - 使用带有数字查找的 having 子句

Question

我有如下的sql语句:

SELECT 
  u.email, 
  sum(completed_on > (now() - interval 7 day)), 
  sum(completed_on > (now() - interval 30 day)),
  count(*)
FROM mturk_flush f JOIN auth_user u ON f.completed_by_id=u.id
WHERE completed_by_id IS NOT NULL
GROUP BY completed_by_id
HAVING 3 != 0
ORDER BY 3 DESC

请注意以下确实有效:

HAVING sum(completed_on > (now() - interval 30 day)) != 0

有没有办法使用别名或索引来引用它？

Answer 1

与 Order By 子句不同，Having 不适用于列号。来自 Docs :

[HAVING where_condition]
[ORDER BY {col_name | expr | position}

现在，我建议您永远不要使用列号，即使使用 Order By 也不行。它很容易出错，并且会降低代码的清晰度和 future 维护者的可读性。

您可以改为定义别名和 use them在 Group By、Order By 和 Having 子句中。

SELECT 
  u.email, 
  sum(completed_on > (now() - interval 7 day)) AS sum_7day_diff, 
  sum(completed_on > (now() - interval 30 day)) AS sum_30day_diff,
  count(*)
FROM mturk_flush f JOIN auth_user u ON f.completed_by_id=u.id
WHERE completed_by_id IS NOT NULL
GROUP BY completed_by_id
HAVING sum_30day_diff != 0
ORDER BY sum_30day_diff DESC