mysql - 使用 SUM(something) 作为 a 时字段列表中的未知列

Question

我正在使用以下查询从表中获取 2 列的总和

SELECT a.user, b.user, SUM(a.post = b.post) AS common_p_count,
SUM(a.option = b.option) AS common_r_count,
(common_p_count+common_r_count)
FROM response a, response b
WHERE a.user = '1' AND b.user != '1' group by b.user

但我收到 未知列 common_p_count

我如何在这个查询中使用 common_p_count 和 common_r_count 来排序结果？

谢谢

Answer 1

您不能在 SELECT 中选择已在同一级别定义的列。条款。如果要重用表达式，则必须求助于使用派生表:

SELECT x.*, (common_p_count+common_r_count)
FROM (
  SELECT 
    a.user AS a_user, 
    b.user AS b_user, 
    SUM(a.post = b.post) AS common_p_count,
    SUM(a.option = b.option) AS common_r_count
  FROM response a, response b
  WHERE a.user = '1' AND b.user != '1' group by b.user
) x

或者，当然，您只需重复表达式:

SELECT 
  a.user AS a_user, 
  b.user AS b_user, 
  SUM(a.post = b.post) AS common_p_count,
  SUM(a.option = b.option) AS common_r_count,
  (SUM(a.post = b.post) + SUM(a.option = b.option))
FROM response a, response b
WHERE a.user = '1' AND b.user != '1' group by b.user

如果您只想按该表达式排序，那么无需任何技巧就可以实现(但您仍然无法 SELECT 查询的同一级别上的表达式)

SELECT 
  a.user AS a_user, 
  b.user AS b_user, 
  SUM(a.post = b.post) AS common_p_count,
  SUM(a.option = b.option) AS common_r_count
FROM response a, response b
WHERE a.user = '1' AND b.user != '1' group by b.user
ORDER BY common_p_count + common_r_count

The reason for this is explained in this blog article here

边注

当然，除了上述解释之外，我认为您的查询是不正确的。由于您仅按 b.user 分组，您将获得 a.user 的随机值在我看来，可能你的总和是不正确的，而且你得到了一个偶然的笛卡尔积。但这是另一个问题的主题。