如何在where子句中使用临时列 [英] How to use a temp column in the where clause

问题描述

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

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

例如，此查询:

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

这将显示两列，一列称为product_brand，另一列称为brand_count. brand_count是即时创建的，始终为1或0，具体取决于是否有50个或该品牌的产品.

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.

这一切对我来说都是有意义的，除了我只有在以下查询中选择brand_count = 1时才选择:

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

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

这给了我这个错误:

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

推荐答案

使用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

在GROUP BY之前 评估

WHERE. HAVING在之后评估.

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

这篇关于如何在where子句中使用临时列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！