首页 > 试题广场 >

每个商品的销售总额

[编程题]每个商品的销售总额
  • 热度指数：38368 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
假设你是一个电商平台的数据库工程师，需要编写一个SQL查询来生成每个商品的销售排行榜。你的数据库中有products和orders两张表：
products示例表如下，包括product_id（商品编号）、name（商品名称）和category（商品类别）字段；
product_id name category
1 Product A Category 1
2 Product B Category 1
3 Product C Category 2
4 Product D Category 2
5 Product E Category 3
orders示例表如下，包括order_id（订单编号）、product_id（商品编号）、quantity（销售数量）和order_date（下单日期）字段；
order_id product_id quantity order_date
101 1 5 2023-08-01
102 2 3 2023-08-01
103 3 8 2023-08-02
104 4 10 2023-08-02
105 5 15 2023-08-03
106 1 7 2023-08-03
107 2 4 2023-08-04
108 3 6 2023-08-04
109 4 12 2023-08-05
110 5 9 2023-08-05
使用上述表格，编写一个SQL查询，返回每个商品的销售总量，先按照商品类别升序排序，再按销售总量降序排列，同时包括商品名称和销售总量。此外，还需要在结果中包含每个商品在其所属类别内的排名，排名相同的商品可以按照 product_id 升序排序。
示例输出如下：
product_name total_sales category_rank
Product A 12 1
Product B 7 2
Product D 22 1
Product C 14 2
Product E 24 1
示例1

输入

drop table if exists products;
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(255),
    category VARCHAR(255)
);
-- 插入商品数据
INSERT INTO products (product_id, name, category)
VALUES
    (1, 'Product A', 'Category 1'),
    (2, 'Product B', 'Category 1'),
    (3, 'Product C', 'Category 2'),
    (4, 'Product D', 'Category 2'),
    (5, 'Product E', 'Category 3');


drop table if exists orders;
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    order_date DATE
);
-- 插入订单数据
INSERT INTO orders (order_id, product_id, quantity, order_date)
VALUES
    (101, 1, 5, '2023-08-01'),
    (102, 2, 3, '2023-08-01'),
    (103, 3, 8, '2023-08-02'),
    (104, 4, 10, '2023-08-02'),
    (105, 5, 15, '2023-08-03'),
    (106, 1, 7, '2023-08-03'),
    (107, 2, 4, '2023-08-04'),
    (108, 3, 6, '2023-08-04'),
    (109, 4, 12, '2023-08-05'),
    (110, 5, 9, '2023-08-05');

输出

product_name|total_sales|category_rank
Product A|12|1
Product B|7|2
Product D|22|1
Product C|14|2
Product E|24|1
算法知识视频讲解
添加回答
ChenYng头像 ChenYng 
select 
    name as product_name,
    sum(quantity) as total_sales,
    row_number() over(partition by category order by sum(quantity) desc) as category_rank
from orders o
inner join products p
    on o.product_id = p.product_id
group by name, category
注意用的是 inner join，left join 会返回所有匹配记录，不符合条件的右表会自动填充为 null
发表于 2024-09-08 17:19:36 回复(1)
NancyFeng头像 NancyFeng 
SELECT product_name, 
       total_sales, 
       category_rank
  FROM  
   (SELECT *,
         RANK() OVER(PARTITION BY category ORDER BY total_sales DESC) AS category_rank
     FROM
     (
        SELECT p.name AS product_name,
               p.category,
               SUM(IFNULL(o.quantity,0)) AS total_sales
          FROM products p 
     LEFT JOIN orders o 
            ON p.product_id = o.product_id
      GROUP BY p.name, p.category) AS t
   ORDER BY category, total_sales DESC) AS t2;
我以为要显示所有的product， 呃呃呃呃
发表于 2024-08-11 05:49:30 回复(0)
szywdwd头像 szywdwd 
select name as product_name, sum(quantity) as total_sales, dense_rank() over (partition by category order by sum(quantity) desc) as category_rank
from products join orders on products.product_id = orders.product_id
group by product_name, category, products.product_id
order by category, total_sales desc, products.product_id

发表于 2024-09-14 13:22:23 回复(1)
牛客314124244号头像 牛客314124244号
SELECT
    p.name AS product_name,
    SUM(o.quantity) AS total_sales,
    ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(o.quantity) DESC, p.product_id ASC) AS category_rank
FROM
    products p
    INNER JOIN orders o ON p.product_id = o.product_id
GROUP BY
    p.name, p.category, p.product_id

发表于 2024-08-14 15:14:54 回复(0)
忙碌的伊泽瑞尔准备进厂头像 忙碌的伊泽瑞尔准备进厂 
with t1 as(
select
p.product_id,
name,
category,
sum(quantity) total_sales
from
products p join orders o
on p.product_id = o.product_id
group by name,category,p.product_id
)
select
name as product_name,
total_sales,
rank() over(partition by category order by total_sales desc,product_id ) category_rank
from t1
;

发表于 2024-08-09 21:04:42 回复(0)
4fbd81e头像 4fbd81e
分步来做 

with t1 as(
    select o.*, p.name as product_name, p.category
    from products p
    join orders o 
    on p.product_id=o.product_id
)
,
t2 as(
    select product_id, product_name, category,
    sum(quantity) as total_quantity
    from t1
    group by product_id, product_name, category
),
t3 as(
    select *, row_number() over(partition by category order by total_quantity desc, product_id asc) as category_rank
    from t2
) 

select product_name, sum(total_quantity)  as total_sales, min(category_rank) as category_rank
from t3
group by product_name


发表于 2025-04-26 09:16:21 回复(0)
麻薯仙人头像 麻薯仙人
注意排序规则，题目全一股脑说了
select 
       name as product_name,
       total_sales,
       dense_rank()over(partition by category order by total_sales desc,product_id) as category_rank
from(
select category,name,a.product_id,sum(quantity) as total_sales
from orders a
inner join products b using(product_id)
group by category,name,a.product_id
order by category)c



发表于 2025-03-18 13:22:50 回复(1)
想去东北泡澡的林北准备进厂头像 想去东北泡澡的林北准备进厂 
SELECT
    name product_name,
    SUM(quantity) total_sales,
    ROW_NUMBER()OVER(PARTITION BY category ORDER BY SUM(quantity) DESC, orders.product_id) category_rank
FROM
    products
    JOIN
    orders
    ON
    products.product_id = orders.product_id
GROUP BY
    orders.product_id
ORDER BY
    category,
    total_sales DESC

发表于 2024-08-16 00:18:53 回复(0)
Simon0625头像 Simon0625 
select name product_name, total_sales, 
rank() over(partition by category order by total_sales desc) category_rank
from products p join (select product_id, sum(quantity) total_sales
from orders
group by product_id) o using(product_id)
order by category,total_sales desc,product_id

发表于 2024-08-09 17:10:09 回复(0)
神的水立方头像 神的水立方
select
    product_name,
    total_sales,
    category_rank
from
    (
        select
            t1.product_id as product_id,
            t1.name as product_name,
            sum(t2.quantity) as total_sales,
            t1.category as category,
            row_number() over (
                partition by
                    t1.category
                order by
                    sum(t2.quantity) desc
            ) as category_rank
        from
            products as t1
            join orders as t2 on t1.product_id = t2.product_id
        GROUP BY
            t1.product_id,
            t1.name
    ) as a
order by
    category asc,
    total_sales desc
发表于 2024-06-26 13:32:04 回复(0)
牛客677654913号头像 牛客677654913号 
select 
    t1.name as product_name,
    total_sales,
    row_number() over(partition by t1.category order by total_sales desc, t1.product_id)  as category_rank
from (
    select 
        p.name,
        p.category,
        o.product_id,
        sum(o.quantity) as total_sales
    from orders o 
    inner join products p using(product_id)
    group by p.name, p.category, o.product_id
    order by p.category, total_sales desc
    ) t1

发表于 2025-06-25 17:17:00 回复(0)
牛客60922574号头像 牛客60922574号 
with
    t1 AS (
        SELECT
            category,
            name,
            SUM(quantity) AS total_sales
        FROM
            products p
            JOIN orders o USING (product_id)
        GROUP BY
            category,
            name
        ORDER BY
            category ASC,
            total_sales DESC
    )
SELECT
    t1.name AS product_name,
    t1.total_sales,
    ROW_NUMBER() OVER (
        PARTITION BY
            t1.category
        order by
            total_sales desc,
            product_id ASC
    ) AS category_rank
FROM
    t1
    JOIN products ON t1.name = products.name

发表于 2025-06-24 17:13:39 回复(0)
Judy678头像 Judy678
select name as product_name,sum(quantity) as total_sales,row_number()over(partition by category order by sum(quantity) desc,p.product_id)category_rank
from products p
inner join orders o
on p.product_id=o.product_id
group by name,category,p.product_id;
发表于 2025-06-22 00:24:38 回复(0)
西柚汁源源头像 西柚汁源源
select product_name,total_sales,row_number()over(partition by category order by total_sales desc) category_rank#新定义字段必须用子查询才能用 
from( 
select  name product_name,category,sum(quantity) total_sales#需要谁子查询中要提取出来 
from products p join orders o on p.product_id=o.product_id 
group by name, category#注意！！！select必须全部分组！！ 
order by category,total_sales desc) t1 

发表于 2025-06-17 10:51:29 回复(0)
摇光Alkaidlc头像 摇光Alkaidlc 
SELECT
    p.name AS product_name,
    SUM(o.quantity) AS total_sales,
    RANK() OVER (
        PARTITION BY
            p.category
        ORDER BY
            p.category,
            SUM(o.quantity) DESC,
            o.product_id
    ) AS category_rank
FROM
    products p
    JOIN orders o ON (p.product_id = o.product_id)
GROUP BY
    p.name,
    p.category,
    p.product_id
写的时候挺顺，最后排序的时候纠结了一下，因为order by写在最后报错了
发表于 2025-06-11 16:24:55 回复(0)
谦虚的布拉德offer多多头像 谦虚的布拉德offer多多
WITH
    t1 AS (
        SELECT
            pt.name,
            SUM(ot.quantity) AS total_sales
        FROM
            products AS pt
            INNER JOIN orders AS ot ON ot.product_id = pt.product_id
        GROUP BY
            pt.name
    ),
    t2 AS (
        SELECT
            t1.name,
            t1.total_sales,
            pt.category,
            pt.product_id
        FROM
            t1 AS t1
            LEFT JOIN products AS pt ON pt.name = t1.name
        ORDER BY
            category
            ,total_sales DESC
            ,product_id ASC

    )

SELECT
    name AS product_name
    ,total_sales
    ,ROW_NUMBER() OVER( PARTITION BY category ORDER BY total_sales DESC) AS category_rank
FROM
    t2

发表于 2025-06-08 15:29:45 回复(0)
百事超级大可乐头像 百事超级大可乐
用变量可以吗，虽然我这个是错的...


set @rank=0
set @last_category=0

select product_name,total_sales,category_rank
if(@last_category=category,@rank:=rank+1,@rank:=1)as category_rank,
@last_category:=category as last_category
from products p
join orders o
on p.product_id=o.product_id
order by category,total_sales desc, product_id;




发表于 2025-06-07 17:22:35 回复(0)
在考古的袋鼠很有一套头像 在考古的袋鼠很有一套 
SELECT product_id,name,
sum(quantity) ,dense_rank() over(partition by product_id order by sum(quantity) desc)  as order1
from orders JOIN products USING (product_id)
group by product_id,name

--partition by product_id错误，这会导致每个商品单独分区，所以每个商品在自己的分区内排名都是1（因为没有其他商品比较）。
--正确的做法是全局排序（不适用partition by）或按类别分区排序（partiton by category）

--正确做法：忽略表格名称~ 
SELECT product_id,name,
sum(quantity) ,dense_rank() over(partition by product_id order by sum(quantity) desc)  as order1
from orders JOIN products USING (product_id)
group by product_id,name



发表于 2025-06-07 16:30:13 回复(0)
脚踏实地备战实习头像 脚踏实地备战实习
with tmp
as
(
    select
    orders.product_id
    ,sum(quantity) total_sales
    ,name
    ,category
    from orders
    join products
    on orders.product_id = products.product_id
    group by product_id
)

select
name product_name
,total_sales
,rank()over(partition by category order by total_sales desc,product_id) category_rank
from tmp
发表于 2025-05-29 14:38:50 回复(0)
精致的熊熊在努力存钱头像 精致的熊熊在努力存钱
with
    t as (
        select
            name,
            category,
            quantity
        from
            orders o
            join products p on o.product_id = p.product_id
    ),
    z as (
        select
            name product_name,
            sum(quantity) total_sales,
            category
        from
            t
        group by
            product_name,
            category
    )
select
    product_name,
    total_sales,
    dense_rank() over (
        partition by
            category
        order by
            total_sales desc
    ) category_rank
from
    z
发表于 2025-05-27 19:09:38 回复(0)
提交观点

问题信息

SQL
难度：
95条回答 939浏览

热门推荐

相关试题

每个商品的销售总额

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号