使用 SUM(something) AS 时字段列表中的未知列

我正在使用以下查询从表中获取 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 来获取排序结果？

Thanks

您不能选择在同一级别上定义的列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

其原因在这篇博客文章中进行了解释 https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-of-sql-operations/

边注

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