基于Parch and Posey数据库的SQL数据过滤查询问题排查

需求1：仅检索标准纸张的order_id和quantity_ordered #

你的SQL存在表字段匹配错误：orders表不包含paper_type字段，该字段属于产品表（products），需要关联订单明细表（order_items）和产品表来筛选数据。

错误SQL：

SELECT order_id, quantity_ordered
FROM orders
WHERE paper_type = 'standard paper'

修正后的SQL：

SELECT o.order_id, oi.quantity_ordered
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.paper_type = 'standard paper';

需求2：找出按纸张订购量统计的前10大订单 #

你的SQL错误地从orders表统计订购量，quantity_ordered字段实际在order_items表中，直接从该表分组统计即可。

错误SQL：

SELECT order_id, SUM(quantity_ordered) as total_quantity
FROM orders
GROUP BY order_id
ORDER BY total_quantity DESC
LIMIT 10

修正后的SQL：

SELECT order_id, SUM(quantity_ordered) as total_quantity
FROM order_items
GROUP BY order_id
ORDER BY total_quantity DESC
LIMIT 10;

需求3：识别总订购量超过2000件的大额订单 #

同样，orders表没有quantity_ordered字段，需从order_items表分组后用HAVING筛选总量。

错误SQL：

SELECT order_id, SUM(quantity_ordered) as total_quantity
FROM orders
GROUP BY order_id
HAVING total_quantity > 2000

修正后的SQL：

SELECT order_id, SUM(quantity_ordered) as total_quantity
FROM order_items
GROUP BY order_id
HAVING total_quantity > 2000;

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