求助：SQL中如何查询total_bags与计算临时列的差异行

SQL的执行逻辑是先处理FROM和WHERE子句，再执行SELECT子句，所以你在WHERE里直接引用SELECT中定义的total_calc别名是行不通的——此时这个别名还未被创建。

你可以用两种方式实现需求：

1. 子查询方式 #

SELECT region, total_bags, total_calc
FROM (
  SELECT 
    region,
    total_bags,
    Small_Bags + Large_Bags + XLarge_Bags AS total_calc
  FROM `coursera-data-analyst-356606.avocado_data.avocado_prices`
) AS subquery
WHERE total_bags <> total_calc

2. CTE（公共表表达式）方式（更易读） #

WITH calc_bags AS (
  SELECT 
    region,
    total_bags,
    Small_Bags + Large_Bags + XLarge_Bags AS total_calc
  FROM `coursera-data-analyst-356606.avocado_data.avocado_prices`
)
SELECT region, total_bags, total_calc
FROM calc_bags
WHERE total_bags <> total_calc

两种方法都是先计算出total_calc字段，再基于计算结果过滤出不一致的行。

内容的提问来源于stack exchange，提问作者BenWade