一、题目描述:
给定商品表和销量表,编写sql语句查询每个品类中销量最高的商品。
二、表结构:
1、商品表结构:
1 CREATE TABLE `sku_info` (
2 `sku_no` int NOT NULL AUTO_INCREMENT COMMENT '商品编号',
3 `sku_name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '商品名称',
4 `category` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '品类',
5 PRIMARY KEY (`sku_no`)
6 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
2、销量表结构:
1 CREATE TABLE `sales` (
2 `id` int NOT NULL AUTO_INCREMENT COMMENT '销量编号',
3 `sku_no` int NOT NULL COMMENT '商品编号',
4 `qty` int NOT NULL COMMENT '销量',
5 `order_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单日期',
6 PRIMARY KEY (`id`)
7 ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
三、插入表数据
插入商品信息 :
INSERT INTO
`sku_info`
VALUES
('1', 'sku1', 'cat1'),
('2', 'sku2', 'cat2'),
('3', 'sku3', 'cat1');
插入销量信息:
INSERT INTO
`sales`
VALUES
('1', '1', '3', '2021-08-25 21:42:38'),
('2', '2', '2', '2021-08-25 21:42:43'),
('3', '3', '2', '2021-08-25 21:42:49'),
('4', '3', '2', '2021-08-25 21:42:52');
四、具体思路
题目描述:
给定商品表和销量表,编写sql语句查询每个品类中销量最高的商品。
注意:
销量表中有可能有存在多个相同的商品销量信息。
1、将sku_info表和sales表用关键字LEFT JOIN连接,按商品编号分组并对其销量进行求和。
1 select
2 s.sku_name 商品名,s.category 品类,SUM(qty) 销量
3 from
4 sku_info s LEFT JOIN sales v on s.sku_no = v.sku_no
5 GROUP BY v.sku_no
6 ORDER BY 销量 desc
执行结果:
2、将执行的结果当作临时表进行分组,然后对销量字段进行max函数操作
SELECT
商品名,品类,max(销量) 销量
from (
select
s.sku_name 商品名,s.category 品类,SUM(qty) 销量
from
sku_info s LEFT JOIN sales v on s.sku_no = v.sku_no
GROUP BY v.sku_no ORDER BY 销量 desc
) as temp_table GROUP BY 品类
执行结果: