Oracle 11g SQL数学计算问题：查询符合条件的图书类别及平均折扣后零售价

首先咱们拆解下你的需求：要找出所有图书类别，这些类别的平均折扣后零售价，需要低于所有类别图书的最高平均零售价（这里的“平均零售价”指每个类别自身的零售均价中的最大值），最后显示符合条件的类别及对应的平均折扣后零售价。

先说说你原来查询里的几个核心问题：

• SQL里不能在SELECT的聚合函数中引用同一句子的别名（比如avg(A)里的A），因为聚合计算的顺序在别名生成之前，引擎识别不了这个别名
• WHERE子句是用来过滤单条行数据的，不能直接用聚合函数的结果（比如B<max(C)），聚合结果的过滤得用HAVING或者子查询/CTE来实现
• 你的数据里部分discount是空值（比如FITNESS类别的图书），直接计算retail-discount会得到NULL，导致平均值计算不准确，得用COALESCE(discount, 0)把空折扣当成0来处理

正确的查询写法 #

这里用CTE（公共表表达式）分步计算，逻辑更清晰易懂：

WITH category_stats AS (
    -- 第一步：计算每个类别的平均折扣后零售价和平均零售价
    SELECT 
        category,
        AVG(retail - COALESCE(discount, 0)) AS avg_discounted_retail,
        AVG(retail) AS avg_retail
    FROM books
    GROUP BY category
),
max_global_avg_retail AS (
    -- 第二步：找到所有类别中最高的平均零售价
    SELECT MAX(avg_retail) AS highest_avg_retail
    FROM category_stats
)
-- 第三步：筛选出平均折扣后价低于最高平均零售价的类别
SELECT cs.category, cs.avg_discounted_retail
FROM category_stats cs
CROSS JOIN max_global_avg_retail mar
WHERE cs.avg_discounted_retail < mar.highest_avg_retail
ORDER BY cs.category;

如果不习惯用CTE，也可以用嵌套子查询的写法：

SELECT 
    category,
    AVG(retail - COALESCE(discount, 0)) AS avg_discounted_retail
FROM books
GROUP BY category
HAVING AVG(retail - COALESCE(discount, 0)) < (
    -- 子查询获取所有类别平均零售价的最大值
    SELECT MAX(avg_retail)
    FROM (
        SELECT AVG(retail) AS avg_retail
        FROM books
        GROUP BY category
    ) AS category_avg_retails
);

结果说明 #

根据你提供的数据，所有类别的平均折扣后零售价都低于全局最高的平均零售价（FAMILY LIFE类别的零售均价55.975），所以最终会返回所有类别及其对应的平均折扣后零售价。

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