多表连接查询练习

题目：

1. 查询所有订单的订单ID、客户全名和订单日期

2. 查询状态为 "已发货" 的订单信息，显示订单ID、客户姓名和发货日期

3. 查询每个订单的商品明细，显示订单ID、产品名称、数量和单价

4. 查询有备注的订单项，显示订单ID、产品名称和备注内容

5. 查询使用了运输商的所有订单，显示订单ID、客户姓名和运输商名称

6. 查询积分高于平均积分的客户所下的所有订单

   ---

1.

查询所有订单的订单ID、客户全名和订单日期

 

输入

SELECT orders.order_id, orders.order_date, CONCAT(customers.first_name, ' ', customers.last_name) AS cus_name
FROM orders
INNER JOIN customers ON customers.customer_id=orders.customer_id
ORDER BY orders.order_id;

 输出

详细检查和批改建议 

优点：

1. 完全符合题目要求：

   • 查询所有订单的订单ID、订单日期和客户全名

2. 正确的JOIN语法：

   • 使用INNER JOIN明确表关系

   • 连接条件准确（customer_id匹配）

3. 专业的字段处理：

   • 使用CONCAT组合客户姓名

   • 为组合字段设置别名(AS cus_name)

4. 清晰的排序：

   • 按order_id排序使结果有序

可选的增强建议（非必需）：

1. 使用表别名简化

2. 添加LIMIT（如果数据量很大）

3. 格式化日期显示（如需特定格式）：

   DATE_FORMAT(o.order_date, '%Y-%m-%d') AS formatted_date

---

 

 

2.

查询状态为 "已发货" 的订单信息，显示订单ID、客户姓名和发货日期

 

输入

SELECT orders.order_id, orders.shipped_date, CONCAT(customers.first_name, ' ', customers.last_name) AS cus_name
FROM orders
INNER JOIN customers ON customers.customer_id=orders.customer_id
WHERE status=2
ORDER BY shipped_date;

输出

详细检查和批改建议  

优点：

1. 正确包含了所有必选字段（订单ID、发货日期、客户全名）

2. 使用INNER JOIN正确连接表

3. 准确筛选已发货订单（status=2）

4. 按发货日期排序（符合业务逻辑）

 

建议的优化点：

1. 添加表前缀一致性：

   • status字段缺少orders.前缀（虽然在此查询中不会产生歧义）

2. 使用表别名（最佳实践）

3. 考虑NULL值处理（如果shipped_date可能为NULL）：

   ORDER BY COALESCE(o.shipped_date, '9999-12-31')  -- 将NULL值排到最后

4. 添加状态说明注释（提高可读性）：

   WHERE o.status = 2  -- 2表示'Shipped'状态

---

 

3.

查询每个订单的商品明细，显示订单ID、产品名称、数量和单价

 

输入

SELECT order_items.order_id, products.name, order_items.quantity, order_items.unit_price 
FROM order_items
INNER JOIN products ON products.product_id=order_items.product_id
ORDER BY order_id;

输出 

详细检查和批改建议 

 

优点：

1. 完全符合题目要求：

   • 查询订单项的商品明细

   • 包含订单ID、产品名称、数量和单价

2. 正确的JOIN语法：

   • 使用INNER JOIN明确连接关系

   • 连接条件准确（product_id匹配）

3. 清晰的排序：

   • 按order_id排序使结果有序

4. 简洁高效的写法：

   • 直接从关联表获取所需字段

   • 没有不必要的复杂性

可选的增强建议（非必需）：

1. 使用表别名简化

2. 添加产品ID（便于后续引用）

3. 处理分页（大数据量时）：

   LIMIT 100 OFFSET 0

4. 格式化价格显示：

   FORMAT(oi.unit_price, 2) AS unit_price

---

 

 4.

查询有备注的订单项，显示订单ID、产品名称和备注内容

 

输入

SELECT order_item_notes.order_id, 
       order_item_notes.note,
       products.name
FROM order_item_notes
INNER JOIN products ON products.product_id=order_item_notes.product_id
ORDER BY order_item_notes.note_id;

输出

 

详细检查和批改建议  

优点：

1. 字段选择完全符合题目要求：

   • 订单ID

   • 备注内容

   • 产品名称

2. 正确的JOIN语法：

   • 准确关联了订单项备注和产品表

3. 清晰的排序：

   • 按备注ID排序确保结果有序

可选的进一步优化：

1. 使用表别名（推荐最佳实践）

2. 限制结果数量（大数据量时）

3. 添加筛选条件（如果只需要有内容的备注）：

   WHERE oin.note IS NOT NULL AND oin.note != ''

4. 更业务导向的排序：

   ORDER BY oin.order_id, p.name  -- 先按订单号，再按产品名称

---

 

 5.

查询使用了运输商的所有订单，显示订单ID、客户姓名和运输商名称

输入

SELECT orders.order_id, 
	   CONCAT(customers.first_name,' ', customers.last_name) AS cus_name,
       shippers.name
FROM orders
INNER JOIN customers ON customers.customer_id=orders.customer_id
INNER JOIN shippers ON shippers.shipper_id=orders.shipper_id
WHERE orders.shipper_id IS NOT NULL;

输出

 

详细检查和批改建议 

 

优点：

1. 完全符合题目要求：

   • 查询使用了运输商的订单

   • 显示订单ID、客户全名和运输商名称

2. 正确的多表JOIN：

   • 准确连接了orders-customers和orders-shippers

3. 必要的NULL检查：

   • 使用shipper_id IS NOT NULL确保只查询有运输商的订单

4. 清晰的查询结构

可选的增强建议： 

1. 使用表别名简化

2. 添加排序（虽然题目未要求） 

 

---

 

 

6.

查询积分高于平均积分的客户所下的所有订单

 

输入

SELECT order_id
FROM orders
WHERE customer_id IN (SELECT customer_id
					  FROM customers
                      WHERE points > AVG(points);

输出 

 

 

详细检查和批改建议  

错误原因：

因为在子查询中使用了聚合函数AVG(points)，但没有指定GROUP BY子句，或者没有正确地使用聚合函数。

在MySQL中，不能直接在WHERE子句中使用聚合函数（如AVG()，SUM()等），除非它是在子查询中并且该子查询返回一个单一值。

修改方法：

使用子查询计算平均值。

 

输入

SELECT order_id
FROM orders
WHERE customer_id IN (SELECT customer_id
					  FROM customers
                      WHERE points > (SELECT AVG(points) FROM customers));

输出

注意事项

嵌套三层子查询结构：

• 最内层计算平均积分；
• 中间层筛选高积分客户；
• 外层查询这些客户的订单。

 

---