首页 > 试题广场 >

查询出不同类别商品中，销售金额排名前三且利润率超过 20%的

[编程题]查询出不同类别商品中，销售金额排名前三且利润率超过 20%的
  • 热度指数：470 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
【背景】：电商平台想要评估商品的销售绩效和利润贡献，以便优化商品策略。
【原始表】：
product_category （商品类别）表:
  • product_id (商品 ID): 商品的唯一标识符
  • category_id (类别 ID): 商品所属的类别 ID
  • product_name (商品名称): 商品的名称
sales_and_profit （销售与利润）表:
  • product_id (商品 ID): 商品的唯一标识符，用于关联商品类别表中的商品
  • sales_amount (销售金额): 商品的销售金额
  • cost_amount (成本金额): 商品的成本金额
【要求】：根据上面这两个表格，查询出不同类别商品中，销售金额排名前三且利润率超过 20%的商品信息，包含的字段：商品 ID、商品名称、所属类别 ID、销售金额、利润率（（销售金额 - 成本金额）/ 销售金额）。查询出来的数据先按照类别 ID 升序排列，再按照销售金额降序排列,如果金额一致按照产品ID升序排列。要求查询出来的表格的字段如下：
  • product_id: 商品的唯一标识符。
  • product_name: 商品的名称。
  • category_id: 类别 ID。
  • sales_amount: 销售金额。
  • profit_rate: 利润率。
【示例】
product_category （商品类别）表:

sales_and_profit （销售与利润）表:
【按照要求查询出来的表】
【解释】
上述示例中，商品类别是1的产品ID有1、2、3、4，其中符合销售金额排名前三且利润率超过 20%的商品信息要求的只有产品ID2、3的产品，按照要求排序就是产品ID3排在产品ID2前面
示例1

输入

DROP TABLE IF EXISTS product_category4;
DROP TABLE IF EXISTS sales_and_profit;

-- 创建表
CREATE TABLE product_category (
    product_id INT PRIMARY KEY,
    category_id INT,
    product_name VARCHAR(50)
);

CREATE TABLE sales_and_profit (
    product_id INT PRIMARY KEY,
    sales_amount DECIMAL(10, 2),
    cost_amount DECIMAL(10, 2)
);

-- 插入数据
INSERT INTO product_category (product_id, category_id, product_name)
VALUES (1, 1, '商品 A'),
       (2, 1, '商品 B'),
       (3, 1, '商品 C'),
       (4, 1, '商品 D'),
       (5, 2, '商品 1'),
       (6, 2, '商品 2'),
       (7, 2, '商品 3'),
       (8, 2, '商品 4');

INSERT INTO sales_and_profit (product_id, sales_amount, cost_amount)
VALUES (1, 500, 50),
       (2, 800, 200),
       (3, 8000, 4000),
       (4, 700, 600),
       (5, 800, 500),
       (6, 1000, 500),
       (7, 200, 50),
       (8, 800, 500);

select * from product_category;
select * from sales_and_profit;

输出

product_id|product_name|category_id|sales_amount|profit_rate
3|商品 C|1|8000.00|0.50
2|商品 B|1|800.00|0.75
6|商品 2|2|1000.00|0.50
5|商品 1|2|800.00|0.38
8|商品 4|2|800.00|0.38
算法知识视频讲解
添加回答
喜欢唱跳rap大熊猫在写总结头像 喜欢唱跳rap大熊猫在写总结
select
     a.product_id,
     product_name,
     category_id,
     a.sales_amount,
     round(a.run/a.sales_amount,2) as profit_rate
from
(select
      product_id,
      sales_amount,
      sales_amount-cost_amount as run,
      cost_amount as chenben,
      rank()over(order by sales_amount desc) as rk
from sales_and_profit  
) as a 
inner join product_category p on p.product_id=a.product_id
where rk<4
having profit_rate>0.2
order by category_id asc,sales_amount desc ,product_id asc;
有没有大佬能帮忙看看，我保存并提交的时候，我的实际输出是三个产品 ，但正确的答案显示的是6个产品。答案把排名第4的产品也输出来了。
实际输出 预期输出
... 你期望输出的第 1 至 7 行与实际输出的第 1 至 4 行有不同
1 product_id|product_name|category_id|sales_amount|profit_rate 1 product_id|product_name|category_id|sales_amount|profit_rate
2 3|商品 C|1|8000.00|0.50 2 3|商品 C|1|8000.00|0.50
3 1|商品 A|1|900.00|0.94 3 1|商品 A|1|900.00|0.94
4 2|商品 B|1|800.00|0.75
4 6|商品 2|2|1000.00|0.50 5 6|商品 2|2|1000.00|0.50
6 5|商品 1|2|800.00|0.38
7 8|商品 4|2|800.00|0.38

发表于 2025-06-26 10:00:53 回复(0)
摇光Alkaidlc头像 摇光Alkaidlc 
WITH t1 AS(
    SELECT category_id, pc.product_id,
           RANK() OVER(PARTITION BY category_id ORDER BY sales_amount DESC) AS rk
    FROM sales_and_profit sap
    JOIN product_category pc ON(sap.product_id = pc.product_id)
)
SELECT
    pc.product_id,
    product_name,
    pc.category_id,
    sales_amount,
    ROUND((sales_amount - cost_amount) / sales_amount ,2) AS profit_rate
FROM product_category pc
JOIN sales_and_profit sap ON(pc.product_id = sap.product_id)
JOIN t1 ON(pc.product_id = t1.product_id)
WHERE (sales_amount - cost_amount) / sales_amount > 0.2 AND rk <= 3
ORDER BY pc.category_id, sales_amount DESC, product_id
窗口函数不能直接用在where筛选里面，所以要写cte
发表于 2025-06-24 20:16:25 回复(0)
迟缓的小饼干1头像 迟缓的小饼干1 
with t as (
select p.product_id,
       p.product_name,
       p.category_id,
       s.sales_amount,
       round((s.sales_amount-s.cost_amount)/s.sales_amount,2) as profit_rate,
       row_number() over(partition by p.category_id order by sum(s.sales_amount) desc) as rn
    
from sales_and_profit as s
left join product_category as p
on s.product_id=p.product_id

group by p.product_id,p.product_name,
         p.category_id, s.sales_amount,profit_rate)

select product_id,
       product_name,
       category_id,
       sales_amount,
       profit_rate
from t 
where rn<=3 and profit_rate>'0.2'
order by category_id,sales_amount desc 
用row_number（），记得按商品类别分区partition by p.category_id。别名rn ，然后在主查询中 where rn<=3 过滤

发表于 2025-06-24 19:12:17 回复(0)
姜天润头像 姜天润
利润率公式真是这样算的吗
发表于 2025-06-24 15:16:18 回复(0)
牛客314411609号头像 牛客314411609号 
select
product_id
,product_name
,category_id
,sales_amount
,profit_rate
from
(
    select
    t1.product_id,
    t1.product_name,
    t1.category_id,
    row_number() over(partition by t1.category_id order by t2.sales_amount desc) as rn,
    t2.sales_amount,
    round((t2.sales_amount-t2.cost_amount)/t2.sales_amount,2) as profit_rate
    from product_category t1
    join sales_and_profit t2
    on t1.product_id=t2.product_id
) t
where rn<=3 and profit_rate>0.20
order by category_id,sales_amount desc,product_id

发表于 2025-06-23 15:47:15 回复(0)
花耶菜头像 花耶菜 
with temp as
(select s.product_id,
       product_name,
       category_id,
       sum(sales_amount) AS sales_amount,
       row_number() over (partition by category_id order by sum(sales_amount) desc) AS rk,
       round((sum(sales_amount)-sum(cost_amount))/(sum(sales_amount)),2) AS profit_rate
from sales_and_profit AS s
left join product_category AS p on s.product_id=p.product_id
group by s.product_id,
         product_name)
select product_id,product_name,category_id,sales_amount,profit_rate
from temp
where rk<=3
  and profit_rate>=0.2
order by category_id,sales_amount desc,product_id;

发表于 2025-06-22 12:58:09 回复(0)
牛客527342094号头像 牛客527342094号
SELECT A.product_id,
       A.product_name,
       A.category_id,
       A.sales_amount,
       A.profit_rate
FROM(SELECT PC.product_id,
            product_name,
            category_id,
            ROUND(SUM(sales_amount),2) AS sales_amount,
            ROUND(SUM(sales_amount-cost_amount)/SUM(sales_amount),2) AS profit_rate,
            row_number()over(partition by category_id order by sum(sales_amount) DESC)AS ranking
    FROM product_category AS PC
    JOIN sales_and_profit AS SAP ON SAP.product_id = PC.product_id
    GROUP BY category_id,PC.product_id,product_name
             ) AS A
WHERE profit_rate > 0.2 AND ranking <=3
ORDER BY category_id,sales_amount DESC,product_id;
发表于 2025-06-17 20:45:24 回复(0)
神奇的大菠萝头像 神奇的大菠萝
测试的例子是不是不对？我用了rank<=4的才通过，理论上应该是<4
发表于 2025-06-17 02:18:29 回复(2)
提交观点

问题信息

难度：
8条回答 33浏览

热门推荐

相关试题

查询出不同类别商品中，销售金额排名前三且利润率超过 20%的商品信息

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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