如何在where子句中使用临时列

Why can't I use a temporary column in the where clause?

为什么我不能在where子句中使用临时列?

For example, this query:

例如,这个查询:

Select 
    product_brand, 
    (CASE WHEN COUNT(product_brand)>50 THEN 1 ELSE 0 END) AS brand_count 
FROM 
    products 
WHERE 
    1 
GROUP BY 
    product_brand

This brings up two columns, one called product_brand and one called brand_count. brand_count is created on the fly and is always 1 or 0 depending on whether or not there are 50 or products with that brand.

这会打开两列,一列名为product_brand,另一列名为brand_count。 brand_count是即时创建的,总是1或0,具体取决于是否有50个或该品牌的产品。

All this makes sense to me, except that I can't select only if brand_count = 1 as in this query below:

这一切对我来说都是有意义的,除了我不能只选择brand_count = 1,如下面的查询:

Select 
    product_brand, 
   (CASE WHEN COUNT(product_brand)>50 THEN 1 ELSE 0 END) AS brand_count 
FROM 
    products 
WHERE 
   brand_count = 1 
GROUP BY 
   product_brand

which gives me this error:

这给了我这个错误:

#1054 - Unknown column 'brand_count' in 'where clause'

5 个解决方案

#1

Use HAVING instead:

使用HAVING代替:

Select
    product_brand,
    (CASE WHEN COUNT(product_brand)>50 THEN 1 ELSE 0 END) AS brand_count
  FROM products
  GROUP BY product_brand
  HAVING brand_count = 1

WHERE is evaluated before the GROUP BY. HAVING is evaluated after.

在GROUP BY之前评估WHERE。之后评估HAVING。

#2

Because in SQL the columns are first "selected" and then "projected".

因为在SQL中,列首先“选中”然后“投影”。

#3

You have to use the full clause, so you will need:

您必须使用完整子句,因此您将需要:

Select 
  product_brand, 
  (CASE WHEN COUNT(product_brand)>50 THEN 1 ELSE 0 END) AS brand_count 
FROM products 
WHERE 
  (CASE WHEN COUNT(product_brand)>50 THEN 1 ELSE 0 END)  = 1 
GROUP BY product_brand

This is the same for any calculated field in any SQL statement .

对于任何SQL语句中的任何计算字段,这都是相同的。

To simplify:

Select Max(Points) as Highest where Highest > 10

won't work, but:

不会起作用,但是:

Select Max(Points) as Highest where Max(Points) > 10

will. It's the same in your case.

将。你的情况也一样。

#4

Because it has no idea what that column is until after it's done the processing.

因为在完成处理之前它不知道该列是什么。

If you want to access the column by that name you would have to use a subquery, otherwise you are going to have to qualify the column without the name you gave it, repeating your case statement.

如果要按该名称访问该列,则必须使用子查询,否则您将不必使用您提供的名称限定该列,重复您的case语句。

#5

If I read your intent correctly, you can re-write this query to read:

如果我正确地阅读了您的意图,您可以重新编写此查询以阅读:

Select 
 product_brand,
 COUNT(product_brand) AS brand_count 
FROM 
 products 
GROUP BY 
 product_brand
HAVING 
 COUNT(product_brand) > 50

This will give you all product_brands that have a count > 50 and will also show you the count for each.

这将为您提供计数> 50的所有product_brands,并且还会显示每个的计数。

#1