Select语句使用AVG函数子查询报1242错误问题咨询

错误根因 #

你遇到的1242报错是因为SELECT列表中的子查询属于标量子查询，要求每次只能返回1行1列的结果，原语句中子查询加了GROUP BY pl.productline后会返回所有产品线对应的平均值（多行数），无法和外层主查询的每行一一匹配，因此触发报错。
如果删除子查询的GROUP BY，返回的是所有产品线的全局总平均值，自然无法实现按产品线分组统计的需求。

修正方案 #

将子查询改为关联子查询，增加与外层当前行的产品线匹配条件，让子查询每次仅计算当前行对应产品线的全局平均值即可，修正后的代码如下：

SELECT 
    pl.productline, 
    YEAR(orderdate), 
    SUM(quantityordered * priceeach) 'Revenue', 
    AVG(quantityordered * priceeach) as 'max rev', 
    MIN(quantityordered * priceeach) as 'min rev', 
    STD(quantityordered * priceeach) as 'std of rev', 
    COUNT(DISTINCT o.ordernumber) 'Annual # of Orders',
    (SELECT AVG(quantityordered * priceeach) as 'glob rev'
     FROM productlines pl_sub
     LEFT JOIN products p_sub ON pl_sub.productline = p_sub.productline
     LEFT JOIN orderdetails od_sub ON p_sub.productcode = od_sub.productcode
     -- 增加和外层的关联条件，仅计算当前产品线的平均值
     WHERE pl_sub.productline = pl.productline) 'avg global rev'
FROM 
    productlines pl
INNER JOIN 
    products p ON pl.productline = p.productline
INNER JOIN 
    orderdetails od ON p.productcode = od.productcode
INNER JOIN 
    orders o ON od.ordernumber = o.ordernumber
INNER JOIN 
    customers c ON o.customernumber = c.customernumber
WHERE 
    country = 'USA'
GROUP BY 
    pl.productline, YEAR(orderdate)
ORDER BY 
    productline;

如果你的「按产品线的全局平均值」要求统计范围和外层一致（仅统计美国区域的订单数据），也可以用窗口函数实现，语法更简洁：

SELECT 
    pl.productline, 
    YEAR(orderdate), 
    SUM(quantityordered * priceeach) 'Revenue', 
    AVG(quantityordered * priceeach) as 'max rev', 
    MIN(quantityordered * priceeach) as 'min rev', 
    STD(quantityordered * priceeach) as 'std of rev', 
    COUNT(DISTINCT o.ordernumber) 'Annual # of Orders',
    -- 窗口函数按productline分区，计算同产品线所有行的平均值
    AVG(quantityordered * priceeach) OVER(PARTITION BY pl.productline) 'avg global rev'
FROM 
    productlines pl
INNER JOIN 
    products p ON pl.productline = p.productline
INNER JOIN 
    orderdetails od ON p.productcode = od.productcode
INNER JOIN 
    orders o ON od.ordernumber = o.ordernumber
INNER JOIN 
    customers c ON o.customernumber = c.customernumber
WHERE 
    country = 'USA'
GROUP BY 
    pl.productline, YEAR(orderdate)
ORDER BY 
    productline;

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