Home Original page

sql面试题中的一个错误答案

文章路径：数据库/基础/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

image

修改后的写法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;

image

文章中的写法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;

image