一道经典的Mysql面试题

这几天刷到一道面试题，边做边巩固下基础

产品表

productid	产品名	单价
1	name1	110
2	name2	220
3	name3	330
4	name4	440

销售表

ProductID	客户名	订货数量	销售单价
2	m	2	250
1	n	3	110
3	n	1	300
4	m	2	400

1. 将销售表中m客户的产品名为name2的销售单价更换为产品表中相应的单价

2. 写出查询销售表中各客户各自的销售总金额的sql语句

3. 写出查询销售总量第一名和第三名的客户的销售总金额的sql语句

---

首先，个人感觉题目这表结构肯定是有问题的，主键不明确，不管那么多了，先建表插数据！

产品表：

DROP TABLE IF EXISTS `product`;

CREATE TABLE `product` (

`productid` int NOT NULL,

`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,

`price` decimal(10, 2) NULL DEFAULT NULL,

PRIMARY KEY (`productid`) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `product` VALUES (1, 'name1', 110.00);

INSERT INTO `product` VALUES (2, 'name2', 220.00);

INSERT INTO `product` VALUES (3, 'name3', 330.00);

INSERT INTO `product` VALUES (4, 'name4', 440.00);

SET FOREIGN_KEY_CHECKS = 1;

销售表我又插入了几条数据好做测试：

DROP TABLE IF EXISTS `sale`;

CREATE TABLE `sale` (

`productid` int NULL DEFAULT NULL,

`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,

`ammount` int NULL DEFAULT NULL,

`saleprice` decimal(10, 2) NULL DEFAULT NULL

) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `sale` VALUES (1, 'n', 3, 110.00);

INSERT INTO `sale` VALUES (2, 'm', 2, 250.00);

INSERT INTO `sale` VALUES (3, 'n', 1, 300.00);

INSERT INTO `sale` VALUES (4, 'm', 2, 400.00);

INSERT INTO `sale` VALUES (3, 'o', 2, 330.00);

INSERT INTO `sale` VALUES (2, 'p', 5, 220.00);

问题一：将销售表中m客户的产品名为name2的销售单价更换为产品表中相应的单价：

UPDATE product a LEFT JOIN sale b on a.productid = b.productid SET b.saleprice = a.price where a.name = 'name2' AND b.username = 'm';

结果：

问题二：写出查询销售表中各客户各自的销售总金额的sql语句

SELECT username, SUM(ammount * saleprice) max_saleprice FROM sale GROUP BY username;

结果：

问题三：写出查询销售总量第一名和第三名的客户的销售总金额的sql语句

这里考虑到重分排名不影响后面名次，所以用dense_rank

SELECT * FROM (SELECT A.username,A.max_ammount,A.max_saleprice, DENSE_RANK() OVER(ORDER BY A.max_ammount DESC) max_rank FROM (SELECT username, SUM(ammount) max_ammount ,SUM(ammount * saleprice) max_saleprice FROM sale GROUP BY(username)) A) B WHERE B.max_rank IN (1,3)

结果：

如有问题请指正，谢谢！