sql面试题中的一个错误答案 #2402
Description
文章路径：数据库/基础/SQL/SQL常见面试题总结（1）/使用子查询/返回每个顾客不同订单的总金额
部分原文如下：
返回每个顾客不同订单的总金额
我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。
OrderItems 表代表订单信息，OrderItems 表有订单号 order_num、商品售出价格 item_price、商品数量 quantity。
| order_num | item_price | quantity |
|---|---|---|
| a0001 | 10 | 105 |
| a0002 | 1 | 1100 |
| a0002 | 1 | 200 |
| a0013 | 2 | 1121 |
| a0003 | 5 | 10 |
| a0003 | 1 | 19 |
| a0003 | 7 | 5 |
Orders 表有订单号 order_num、顾客 id cust_id。
| order_num | cust_id |
|---|---|
| a0001 | cust10 |
| a0002 | cust1 |
| a0003 | cust1 |
| a0013 | cust2 |
【问题】
编写 SQL 语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回 total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序。
答案：
# 写法 1：子查询
SELECT o.cust_id AS cust_id, tb.total_ordered AS total_ordered
FROM (SELECT order_num, Sum(item_price * quantity) AS total_ordered
FROM OrderItems
GROUP BY order_num) AS tb,
Orders o
WHERE tb.order_num = o.order_num
ORDER BY total_ordered DESC
# 写法 2：连接表
SELECT b.cust_id, Sum(a.quantity * a.item_price) AS total_ordered
FROM OrderItems a,Orders b
WHERE a.order_num = b.order_num
GROUP BY cust_id
ORDER BY total_ordered DESC这道题应该是想求每个顾客下的所有订单的总金额，并按照总金额从大到小进行排序。
文章中的“写法2：连接表”的 sql 是没有问题的，先以 order_num 为连接条件进行连表查询，然后按照 cust_id 进行分组，就可以计算每个顾客的所有订单的总和。
但是“写法 1：子查询”，先子查询得到每个订单的总金额，然后就只有根据 order_num 连表查询了，但是 cust_id 与 order_num 是一对多的关系，即一个顾客会有多个订单，因此在连表查询后还是需要对 cust_id 分组。
因此，写法1中还需要加上对 cust_id 的分组才是对的，即：
SELECT o.cust_id, SUM(tb.total_ordered) AS `total_ordered`
FROM (SELECT order_num, SUM(item_price * quantity) AS total_ordered
FROM OrderItems
GROUP BY order_num) AS tb,
Orders o
WHERE tb.order_num = o.order_num
GROUP BY o.cust_id
ORDER BY total_ordered DESC;同时建议修改一下文中的“并使用子查询返回 total_ordered 以便返回每个顾客的订单总数”，这个订单总数有点歧义，建议修改成“并使用子查询返回每个订单的总金额 total_ordered”
测试：
CREATE TABLE `OrderItems`(
`order_num` VARCHAR(16) NOT NULL COMMENT '订单号',
`item_price` INT UNSIGNED NOT NULL COMMENT '商品出售价格',
`quantity` INT UNSIGNED NOT NULL COMMENT '商品数量'
) COMMENT='订单商品信息表';
INSERT INTO `OrderItems` VALUES
('a0001', 10, 105),
('a0002', 1, 1100),
('a0002', 1, 200),
('a0013', 2, 1121),
('a0003', 5, 10),
('a0003', 1, 19),
('a0003', 7, 5);
CREATE TABLE `Orders`(
`order_num` VARCHAR(16) NOT NULL COMMENT '订单号',
`cust_id` VARCHAR(16) NOT NULL COMMENT '顾客 id'
) COMMENT='订单表';
INSERT INTO `Orders` VALUES
('a0001', 'cust10'),
('a0002', 'cust1'),
('a0003', 'cust1'),
('a0013', 'cust2');文章中的写法1测试结果：
SELECT o.cust_id AS cust_id, tb.total_ordered AS total_ordered
FROM (SELECT order_num, Sum(item_price * quantity) AS total_ordered
FROM OrderItems
GROUP BY order_num) AS tb,
Orders o
WHERE tb.order_num = o.order_num
ORDER BY total_ordered DESC
修改后的写法1测试结果：
SELECT o.cust_id, SUM(tb.total_ordered) AS `total_ordered`
FROM (SELECT order_num, SUM(item_price * quantity) AS total_ordered
FROM OrderItems
GROUP BY order_num) AS tb,
Orders o
WHERE tb.order_num = o.order_num
GROUP BY o.cust_id
ORDER BY total_ordered DESC;
文章中的写法2测试结果：
SELECT b.cust_id, SUM(a.quantity * a.item_price) AS total_ordered
FROM OrderItems a,Orders b
WHERE a.order_num = b.order_num
GROUP BY cust_id
ORDER BY total_ordered DESC;