计算房屋天花板与地板Qty列百分比差异（筛选超10%房屋）

需求说明 #

• 统计每栋房屋中，天花板类（所有以Ceiling开头的Item）和地板类（所有以Floor开头的Item）的Qty总和
• 使用公式计算两者的百分比差异：|V1-V2|/((V1+V2)/2)*100（其中V1为天花板总Qty，V2为地板总Qty）
• 筛选出差异超过10%且天花板面积大于地板面积的房屋

数据表「Property_1」结构及数据 #

期望结果 #

待完善的初始SQL #

SELECT 
    (SELECT SUM(Qty) FROM Property_1 WHERE Item IN ('Ceiling type 1', 'Ceiling type 2', 'Ceiling type 3')) -
    (SELECT SUM(Qty) FROM Property_1 WHERE Item IN ('Floor type 1', 'Floor type 2'))
AS Total_Qty_Difference
GROUP BY House_ID;

完善后的SQL解决方案 #

SELECT 
    House_ID,
    ROUND(
        ABS(ceiling_total - floor_total) / ((ceiling_total + floor_total)/2) * 100
    ) AS Difference_%
FROM (
    SELECT 
        House_ID,
        SUM(CASE WHEN Item LIKE 'Ceiling%' THEN Qty ELSE 0 END) AS ceiling_total,
        SUM(CASE WHEN Item LIKE 'Floor%' THEN Qty ELSE 0 END) AS floor_total
    FROM Property_1
    GROUP BY House_ID
) AS house_totals
WHERE 
    ceiling_total > floor_total
    AND ABS(ceiling_total - floor_total) / ((ceiling_total + floor_total)/2) * 100 > 10
ORDER BY House_ID;

关键改进点 #

1. 分组统计更高效：用子查询按House_ID分组，一次性计算每栋房屋的天花板和地板总Qty，修正原SQL中未关联分组的错误子查询逻辑
2. 兼容更多Item类型：用LIKE 'Ceiling%'和LIKE 'Floor%'匹配所有同类物品，无需硬编码具体的type值，扩展性更强
3. 实现需求筛选：添加WHERE条件，精准筛选出天花板面积更大且差异超过10%的房屋
4. 格式化结果：用ROUND函数将百分比差异取整，与期望结果格式完全匹配

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