为何GROUP BY子句使用Description_lenght_buckets提示列名无效？

你遇到的问题根源是：多数SQL数据库（比如SQL Server、Oracle）的执行顺序中，GROUP BY会在SELECT之前运行，此时你定义的别名Description_lenght_buckets还未被创建，所以数据库无法识别这个列名。完全不需要通过ALTER语句添加新列，有三种更直接的解决方式：

方法1：重复CASE表达式到GROUP BY中 #

直接把SELECT里的CASE逻辑完整复制到GROUP BY后面，数据库就能基于这个逻辑完成分组：

SELECT
CASE
WHEN LEN(b.app_desc) < 500 THEN 'Short'
WHEN LEN(b.app_desc) BETWEEN 500 AND 1000 THEN 'Medium'
ELSE 'Long'
END AS Description_lenght_buckets, AVG(a.user_rating) AS Avg_rating
FROM AppleStore AS a
JOIN appleStore_description AS b
ON a.id = b.id
GROUP BY 
CASE
WHEN LEN(b.app_desc) < 500 THEN 'Short'
WHEN LEN(b.app_desc) BETWEEN 500 AND 1000 THEN 'Medium'
ELSE 'Long'
END
ORDER BY Avg_rating

方法2：使用CTE（公共表表达式） #

先通过CTE提前计算出分组字段，再在外层查询中分组，可读性更好：

WITH AppDescLength AS (
    SELECT
        a.user_rating,
        CASE
        WHEN LEN(b.app_desc) < 500 THEN 'Short'
        WHEN LEN(b.app_desc) BETWEEN 500 AND 1000 THEN 'Medium'
        ELSE 'Long'
        END AS Description_lenght_buckets
    FROM AppleStore AS a
    JOIN appleStore_description AS b
    ON a.id = b.id
)
SELECT Description_lenght_buckets, AVG(user_rating) AS Avg_rating
FROM AppDescLength
GROUP BY Description_lenght_buckets
ORDER BY Avg_rating

方法3：使用子查询 #

和CTE逻辑类似，通过子查询生成包含分组字段的临时结果，再在外层分组：

SELECT Description_lenght_buckets, AVG(user_rating) AS Avg_rating
FROM (
    SELECT
        a.user_rating,
        CASE
        WHEN LEN(b.app_desc) < 500 THEN 'Short'
        WHEN LEN(b.app_desc) BETWEEN 500 AND 1000 THEN 'Medium'
        ELSE 'Long'
        END AS Description_lenght_buckets
    FROM AppleStore AS a
    JOIN appleStore_description AS b
    ON a.id = b.id
) AS TempTable
GROUP BY Description_lenght_buckets
ORDER BY Avg_rating

补充说明：像MySQL、PostgreSQL这类数据库支持在GROUP BY中直接使用SELECT别名，但这属于方言特性，并非SQL标准。如果你的代码需要兼容多数据库，建议优先使用上述三种标准方法。

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