查询catalog表中售价超对应零件平均成本的供应商SID并显示平均成本

嘿，我来帮你调整SQL语句，让它输出你需要的四列！首先咱们看一下原查询的问题：你已经在WHERE子句里算出了每个零件的平均成本，但没把这个值放到SELECT列表里，所以结果里就缺了avg_cost这一列。另外原查询里的GROUP BY和DISTINCT其实有点重复，咱们可以优化一下。

方法一：将子查询加入SELECT列表 #

直接把计算平均成本的子查询放到SELECT字段中，这样每一行都会对应到对应pid的平均成本，同时保留原有的过滤条件：

SELECT DISTINCT 
    c.sid, 
    c.pid, 
    c.cost,
    (SELECT AVG(c1.cost) FROM catalog c1 WHERE c1.pid = c.pid) AS avg_cost
FROM catalog AS c
WHERE c.cost > (SELECT AVG(c1.cost) FROM catalog c1 WHERE c1.pid = c.pid);

• 这里的DISTINCT可以确保不会出现重复的sid-pid-cost-avg_cost组合，如果你的数据里本来就没有重复项，也可以去掉它。
• 缺点是数据库可能会对每个行都执行一次子查询计算平均，数据量大的时候效率稍低。

方法二：使用窗口函数（更高效推荐） #

如果你的数据库支持窗口函数（比如MySQL 8.0+、PostgreSQL、SQL Server等），用这种方法只需要扫描一次表，效率更高：

SELECT sid, pid, cost, avg_cost
FROM (
    SELECT 
        sid, 
        pid, 
        cost,
        AVG(cost) OVER (PARTITION BY pid) AS avg_cost
    FROM catalog
) AS sub_query
WHERE cost > avg_cost;

• 内层查询通过AVG(cost) OVER (PARTITION BY pid)给每个pid分组计算平均成本，把这个值附加到每一行上。
• 外层查询只过滤出成本高于对应pid平均成本的记录，这样就得到了你需要的四列，而且不需要额外去重，逻辑更清晰。

两种方法都能满足你的需求，优先推荐窗口函数的写法，尤其是数据量较大的时候。

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