SQL 为什么在GROUP BY Description_lenght_buckets中报错“无效的列名”

问题描述

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 Description_lenght_buckets
ORDER BY Avg_rating

我尝试的唯一方法是使用ALTER创建新列，然后使用GROUP BY，这样就可以工作。

这个代码能够工作的唯一方式是使用ALTER吗？

解决方案

你可以通过子查询来实现：

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

说明：您的子查询选择要聚合的个别记录。