SQLite中如何获取总订单数及正确生成fulfillment_summary

你的问题出在子查询表别名的作用域冲突上：外层查询通过LEFT JOIN关联了Warehouse_Orders并命名为Orders，而子查询里又用了相同的Orders别名，SQLite会误以为子查询中的Orders是外层关联后的结果集，而非原始的Warehouse_Orders表，导致总订单数计算错误。此外，SQLite的整数除法规则也会导致百分比计算失真，需要额外处理。

修复方案1：避免别名冲突，修正整数除法 #

修改子查询中的表别名（或直接不使用别名），同时将整数转换为浮点数以确保百分比计算准确：

SELECT
    Warehouse.warehouse_id,
    Warehouse.state || ': ' || Warehouse.warehouse_alias AS warehouse_name,
    COUNT(Orders.order_id) AS number_of_orders,
    -- 子查询使用独立的表引用，避免和外层别名冲突
    (SELECT COUNT(*) FROM Warehouse_Orders) AS total_orders,
    CASE
        -- 乘以1.0将整数转为浮点数，避免整数除法失真
        WHEN COUNT(Orders.order_id) * 1.0 / (SELECT COUNT(*) FROM Warehouse_Orders) <= 0.20
        THEN 'Fulfilled 0-20% of Orders'
        WHEN COUNT(Orders.order_id) * 1.0 / (SELECT COUNT(*) FROM Warehouse_Orders) > 0.20
        AND COUNT(Orders.order_id) * 1.0 / (SELECT COUNT(*) FROM Warehouse_Orders) <= 0.60
        THEN 'Fulfilled 21-60% of Orders'
        ELSE 'Fulfilled more than 60% of Orders'
    END AS fulfillment_summary
FROM Warehouse_Stats Warehouse
LEFT JOIN Warehouse_Orders Orders
ON Warehouse.warehouse_id = Orders.warehouse_id
GROUP BY
    Warehouse.warehouse_id,
    warehouse_name
HAVING
    COUNT(Orders.order_id) > 1

修复方案2：使用CTE简化计算（更高效易读） #

用公共表表达式（CTE）提前计算总订单数，只执行一次统计，同时避免重复子查询：

WITH TotalOrders AS (
    -- 一次性计算总订单数
    SELECT COUNT(*) AS total FROM Warehouse_Orders
)
SELECT
    Warehouse.warehouse_id,
    Warehouse.state || ': ' || Warehouse.warehouse_alias AS warehouse_name,
    COUNT(Orders.order_id) AS number_of_orders,
    TO.total AS total_orders,
    CASE
        WHEN COUNT(Orders.order_id) * 1.0 / TO.total <= 0.20
        THEN 'Fulfilled 0-20% of Orders'
        WHEN COUNT(Orders.order_id) * 1.0 / TO.total > 0.20
        AND COUNT(Orders.order_id) * 1.0 / TO.total <= 0.60
        THEN 'Fulfilled 21-60% of Orders'
        ELSE 'Fulfilled more than 60% of Orders'
    END AS fulfillment_summary
FROM Warehouse_Stats Warehouse
LEFT JOIN Warehouse_Orders Orders
ON Warehouse.warehouse_id = Orders.warehouse_id
-- 通过CROSS JOIN将总订单数关联到每一行
CROSS JOIN TotalOrders TO
GROUP BY
    Warehouse.warehouse_id,
    warehouse_name,
    TO.total
HAVING
    COUNT(Orders.order_id) > 1

关键说明 #

1. 别名冲突：子查询中使用与外层相同的表别名会导致SQL引擎混淆表的来源，必须确保子查询引用的是原始表而非关联后的结果集。
2. 整数除法：SQLite中两个整数相除会自动取整（例如3/10=0），乘以1.0可将运算转换为浮点数除法，得到正确的百分比结果。

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