某宝店铺的SPU数量

item_id	style_id	tag_price	inventory
A001	A	100	20
A002	A	120	30
A003	A	200	15
B001	B	130	18
B002	B	150	22
B003	B	125	10
B004	B	155	12
C001	C	260	25
C002	C	280	18

请你统计每款的SPU（货号）数量，并按SPU数量降序排序，以上例子的输出结果如下：

style_id	SPU_num
B	4
A	3
C	2

示例1

输入:

drop table if exists product_tb;
CREATE TABLE product_tb(
item_id char(10) NOT NULL,
style_id char(10) NOT NULL,
tag_price int(10) NOT NULL,
inventory int(10) NOT NULL
);
INSERT INTO product_tb VALUES('A001', 'A', 100,  20);
INSERT INTO product_tb VALUES('A002', 'A', 120, 30);
INSERT INTO product_tb VALUES('A003', 'A', 200,  15);
INSERT INTO product_tb VALUES('B001', 'B', 130, 18);
INSERT INTO product_tb VALUES('B002', 'B', 150,  22);
INSERT INTO product_tb VALUES('B003', 'B', 125, 10);
INSERT INTO product_tb VALUES('B004', 'B', 155,  12);
INSERT INTO product_tb VALUES('C001', 'C', 260, 25);
INSERT INTO product_tb VALUES('C002', 'C', 280,  18);

输出:

B|4
A|3
C|2

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 36ms, 内存消耗: 6404KB, 提交时间: 2022-01-01

select style_id,count(item_id) as SPU_num
from product_tb
group by style_id
order by  SPU_num desc

Mysql 解法, 执行用时: 36ms, 内存消耗: 6416KB, 提交时间: 2021-12-07

SELECT style_id,count(style_id) as SPU_num
from product_tb
GROUP BY style_id
ORDER by SPU_num DESC

Mysql 解法, 执行用时: 36ms, 内存消耗: 6524KB, 提交时间: 2021-12-31

select style_id,count(style_id) as SPU_num
from product_tb
group by style_id
order by SPU_num desc

Mysql 解法, 执行用时: 36ms, 内存消耗: 6628KB, 提交时间: 2022-02-09

SELECT style_id,
COUNT(1) spu
FROM product_tb
GROUP BY style_id
order by COUNT(1) desc

Mysql 解法, 执行用时: 37ms, 内存消耗: 6384KB, 提交时间: 2021-12-22

SELECT
    style_id,
    COUNT(DISTINCT item_id) AS SPU_num
FROM product_tb
GROUP BY style_id
ORDER BY SPU_num DESC

上一题

下一题

详情