使用SUM(something)AS时字段列表中的未知列 [英] Unknown column in field list when using SUM(something) AS a

问题描述

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

I am using following query for getting sum of 2 columns from a table

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来订购结果?

How can i use common_p_count and common_r_count in this query for ordering result?

谢谢

推荐答案

您不能选择在SELECT子句中在同一级别上定义的列.如果要重用表达式，则必须使用派生表:

You cannot select a column that you've defined on the same level in your SELECT clause. If you want to reuse an expression, you have to resort to using a derived table:

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

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

Or, of course, you simply repeat the expression:

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在查询的同一级别上使用该表达式)

If you just want to order by that expression, then that's possible without any tricks (but you still cannot SELECT the expression on the same level of your query)

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

当然，除了上述说明之外，我认为您的查询不正确.由于您仅按b.user进行分组，因此我认为a.user会得到一个随机值，并且您的总和可能是不正确的，并且会得到意外的笛卡尔积.但这是另一个问题的话题.

Apart from the above explanations, of course, I don't think your query is correct. Since you're grouping only by b.user, you'll get a random value for a.user and probably your sums are incorrect as well as you get an accidental cartesian product, in my opinion. But that's a topic for another question.

这篇关于使用SUM(something)AS时字段列表中的未知列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！