首页 > 试题广场 >

哪些产品在特定时间段内表现最为出色

[编程题]哪些产品在特定时间段内表现最为出色
  • 热度指数：1330 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
【背景】：公司需要对过去一段时间的销售情况进行深入分析，以了解哪些产品在特定时间段内表现最为出色。
【原始表】：
products（产品）表:
  • product_id (产品 ID): 产品的唯一标识符
  • product_name (产品名称): 产品的名称

sales_records（销售记录）表:
  • sales_id (销售 ID): 销售记录的唯一标识符
  • product_id (产品 ID): 产品的唯一标识符，用于关联产品表中的产品
  • sales_date (销售日期): 销售发生的日期
  • sales_amount (销售金额): 该次销售的金额
  • sales_quantity (销售数量): 该次销售的产品数量

【要求】：根据上面这两个表格，查询在'2024-01-01' —'2024-12-31'销量最高的产品，包含的字段：产品 ID、产品名称、总销售额、总销量。如果存在销量一样的多个产品，都展示出来且按照产品ID升序排列，要求查询出来的表格的字段如下：
  • product_id: 产品的唯一标识符。
  • product_name: 产品的名称。
  • total_sales_amount: 总销售额。
  • total_sales_quantity: 总销量。
【示例】：
products（产品）表:

sales_records（销售记录）表:
【按照要求查询出来的表】
【解释】：2024年的订单有4笔，其中产品ID是1和3的销量最高，都是16，所以查询出来产品ID是1和3的产品，他们的总销售金额也一样都是900
示例1

输入

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS sales_records;
-- 创建表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50)
);

CREATE TABLE sales_records (
    sales_id INT PRIMARY KEY,
    product_id INT,
    sales_date DATE,
    sales_amount DECIMAL(10, 2),
    sales_quantity INT
);

-- 插入数据
INSERT INTO products (product_id, product_name)
VALUES (1, '产品 A'),
       (2, '产品 B'),
       (3, '产品 C'),
       (4, '产品 D');

INSERT INTO sales_records (sales_id, product_id, sales_date, sales_amount, sales_quantity)
VALUES (1, 1, '2024-03-05', 500.00, 10),
       (2, 2, '2024-05-10', 300.00, 8),
       (3, 1, '2024-06-20', 400.00, 6),
       (4, 3, '2024-06-20', 900.00, 16);

select * from products;
select * from sales_records;

输出

product_id|product_name|total_sales_amount|total_sales_quantity
1|产品 A|900.00|16
3|产品 C|900.00|16
算法知识视频讲解
添加回答
菜的雅痞头像 菜的雅痞 
select product_id,
    product_name,
    sum(sales_amount) as total_sales_amount,
    sum(sales_quantity) as total_sales_quantity
from products
join sales_records
using (product_id)
where sales_date between '2024-01-01' and '2024-12-31'
group by 1,2
having total_sales_quantity >= all(
    select sum(sales_quantity) over(
        partition by product_id
    )
    from sales_records as s
    where sales_date between '2024-01-01' and '2024-12-31'
)

发表于 2025-07-09 14:05:04 回复(0)
牛客128725104号头像 牛客128725104号
select a.product_id,a.product_name, a.total_sales_amount, a.total_sales_quantity
from(
    select p.product_id, p.product_name, sum(s.sales_amount) total_sales_amount, max(sum(s.sales_quantity))over() max_sales_quantity, sum(s.sales_quantity) total_sales_quantity
    from products p left join sales_records s on p.product_id=s.product_id
    where s.sales_date between '2024-01-01' and '2024-12-31'
    group by p.product_id, p.product_name
    order by p.product_id
)a
where a.total_sales_quantity=a.max_sales_quantity
order by a.product_id
发表于 2025-07-07 17:52:50 回复(0)
LOGlog头像 LOGlog 
WITH
    c_s AS (
        SELECT
            product_id,
            sum(sales_quantity) AS sales_qty,
            sum(sales_amount) AS sales_amt
        FROM
            sales_records s
        WHERE
            sales_date >= '2024-01-01' && sales_date <= '2024-12-31'
        GROUP BY
            product_id
    ),
    max_qty AS (
        SELECT
            max(sales_qty) m_qty
        FROM
            c_s
    )
SELECT distinct
    p.product_id,
    p.product_name,
    c_s.sales_amt AS "total_sales_amount",
    c_s.sales_qty AS "total_sales_quantity"
FROM
    products p,
    sales_records s,
    c_s,
    max_qty
WHERE
    p.product_id = c_s.product_id && p.product_id = s.product_id && c_s.sales_qty = max_qty.m_qty
ORDER BY
    total_sales_quantity DESC,
    p.product_id ASC;

    


发表于 2025-07-07 00:51:49 回复(0)
Judy678头像 Judy678
select product_id,product_name,total_sales_amount,total_sales_quantity
from (select p.product_id as product_id,
product_name,
sum(sales_amount) as total_sales_amount,
sum(sales_quantity) as total_sales_quantity,
dense_rank()over(order by sum(sales_quantity)desc ) as rk
from products p join sales_records s using(product_id)
where sales_date between '2024-01-01' and '2024-12-31'
group by p.product_id,product_name) a
where rk=1
order by product_id;
发表于 2025-07-06 18:31:05 回复(0)
牛客131088855号头像 牛客131088855号 
select
product_id
,product_name
,total_sales_amount
,total_sales_quantity
from
(
    select
    *
    ,dense_rank()over( order by total_sales_quantity desc) rk 
    from
    (
        select
        sales_records.product_id 
        ,product_name 
        ,sum(sales_amount) total_sales_amount
        ,sum(sales_quantity) total_sales_quantity
        from products left join sales_records on
        sales_records.product_id =products.product_id
        where sales_date between '2024-01-01' and '2024-12-31'
        group by 1,2
    ) a 
) b 
where rk=1
order by 1

发表于 2025-07-05 12:35:16 回复(0)
牛客849458087号头像 牛客849458087号 
with
    t1 as (
        select
            a.product_id,
            product_name,
            sum(sales_amount) total_sales_amount,
            sum(sales_quantity) total_sales_quantity,
            rank() over (
                order by
                    sum(sales_quantity) desc
            ) rk
        from
            products a
            join sales_records b on a.product_id = b.product_id
        group by
            a.product_id
        order by
            a.product_id
    )
select
    product_id,
    product_name,
    total_sales_amount,
    total_sales_quantity
from
    t1
where
    rk = 1
order by
    product_id

发表于 2025-07-04 17:00:44 回复(0)
牛客284342321号头像 牛客284342321号
with t as(select product_id,product_name,sum(sales_amount) total_sales_amount,sum(sales_quantity) total_sales_quantity,
      rank() over(
        order by sum(sales_quantity) desc
      ) px
from sales_records s
left join products p
using(product_id)
where sales_date between '2024-01-01' and '2024-12-31'
group by product_id,product_name)
select product_id,product_name,total_sales_amount,total_sales_quantity
from t
where px=1
order by product_id
发表于 2025-07-03 12:54:37 回复(0)
摇光Alkaidlc头像 摇光Alkaidlc 
SELECT
    product_id,
    product_name,
    total_sales_amount,
    total_sales_quantity
FROM(SELECT
    p.product_id,
    product_name,
    SUM(sales_amount) AS total_sales_amount,
    SUM(sales_quantity) AS total_sales_quantity,
    RANK() OVER(ORDER BY SUM(sales_quantity) DESC) AS rk
FROM sales_records sr
JOIN products p ON(sr.product_id = p.product_id)
WHERE sales_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY p.product_id, product_name) AS t1
WHERE rk = 1
ORDER BY product_id
用一个from子查询
发表于 2025-07-02 22:29:49 回复(0)
迟缓的小饼干1头像 迟缓的小饼干1 
with t as (
select  p.product_id,
        p.product_name,
        sum(s.sales_amount) as total_sales_amount,
        sum(s.sales_quantity) as total_sales_quantity,
        dense_rank() over ( order by sum(s.sales_quantity) desc) as rn
       
from sales_records as s
left join products as p
on s.product_id=p.product_id

where year(s.sales_date)='2024'
group by  p.product_id)


select product_id,
       product_name,
       total_sales_amount,
       total_sales_quantity
from t
where rn=1
order by product_id

发表于 2025-06-27 17:40:57 回复(0)
Raxxl头像 Raxxl
select
product_id,product_name,total_sales_amount,total_sales_quantity
from (
    select
    a.product_id,product_name,total_sales_amount,total_sales_quantity,
    rank()over( order by total_sales_quantity desc) as rk
from (
    select product_id, sum(sales_amount) as total_sales_amount  
    , sum(sales_quantity) as  total_sales_quantity
    from sales_records
    where sales_date between '2024-01-01' and '2024-12-31'
    group by product_id
)a
left join products p using(product_id)
)b
where rk = 1;
发表于 2025-06-25 11:40:37 回复(0)
牛客314411609号头像 牛客314411609号 
with temp as
(
    select
    a.product_id,a.product_name,
    sum(b.sales_amount) as total_sales_amount,
    sum(b.sales_quantity) as total_sales_quantity
    from products a
    join sales_records b
    on a.product_id=b.product_id
    where b.sales_date between '2024-01-01' and '2024-12-31'
    group by a.product_id,a.product_name
)

select
product_id,
product_name,
total_sales_amount,
total_sales_quantity
from
(
    select
    product_id,
    product_name,
    total_sales_amount,
    total_sales_quantity,
    dense_rank() over(order by total_sales_quantity desc) as ranking
    from temp
) t
where ranking=1
order by product_id

发表于 2025-06-23 19:37:02 回复(0)
牛客40997913号头像 牛客40997913号
with sales_summary as (
        select
            p.product_id,
            p.product_name,
            sum(s.sales_amount) as total_sales_amount,
            sum(s.sales_quantity) as total_sales_quantity
        from
            products as p
            left outer join sales_records as s on p.product_id = s.product_id
            and s.sales_date between '2024-01-01' AND '2024-12-31'
        group by
            p.product_id,
            p.product_name
    )
select
    product_id,
    product_name,
    total_sales_amount,
    total_sales_quantity    
from
    sales_summary
where
    total_sales_quantity = (
        select
            max(total_sales_quantity)
        from
            sales_summary
    )
order by
    product_id asc;

发表于 2025-06-22 11:06:40 回复(0)
提交观点

问题信息

难度：
12条回答 40浏览

热门推荐

相关试题

哪些产品在特定时间段内表现最为出色

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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