时间戳

首页 > 解决方案 > 查找每个类别中最畅销的 3 种产品

sql - 查找每个类别中最畅销的 3 种产品

问题描述

我试图找到每个类别中最畅销的 3 种产品。但是我尝试的 SQL 并没有产生正确的结果。这是我做的事情：

产品表：

CREATE TABLE [dbo].[Products](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](100) NOT NULL,
    [category] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)
ID 姓名 类别
1 产品1 猫1
2 产品2 猫1
3 产品3 猫1
4 产品4 猫1
5 产品5 猫1
6 产品6 猫2
7 产品7 猫2
8 产品8 猫2
10 产品10 猫2

订单项表：

CREATE TABLE [dbo].[OrderItems](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [prodID] [int] NOT NULL,
    [quantity] [int] NOT NULL,
 CONSTRAINT [PK_OrderItems] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)

ALTER TABLE [dbo].[OrderItems]  WITH CHECK ADD  CONSTRAINT [FK_OrderItems_Products] FOREIGN KEY([prodID])
REFERENCES [dbo].[Products] ([id])

ALTER TABLE [dbo].[OrderItems] CHECK CONSTRAINT [FK_OrderItems_Products]
ID 产品编号 数量
1 1 10
2 2 6
3 2 6
4 4 3
5 3 2
6 6 10
7 7 1

我试过的 SQL：

select *
from (select p1.id, p1.category, (select sum(quantity) from OrderItems as oi where oi.prodID = p1.id) as sales
from Products as p1) as p2
where p2.sales >= (
    select distinct(sales)
    from (select p1.id, p1.category, (select sum(quantity) from OrderItems as oi where oi.prodID = p1.id) as sales
from Products as p1) as p3
    where p2.category = p3.category
    order by sales desc
    offset 2 rows fetch next 1 rows only)

预期结果：

ID 类别 销售量
1 猫1 10
2 猫1 12
4 猫1 3
6 猫2 10
7 猫2 1

实际结果：

ID 类别 销售量
1 猫1 10
2 猫1 12
4 猫1 3

查询中的错误是什么？有没有更好的方法来达到这个结果？

标签： sqlsql-servergroup-bysumwindow-functions

解决方案

您可以加入表格并聚合以获取每种产品的总销售额。
还使用ROW_NUMBER()基于产品类别并按总销售额排序的窗口函数对每个产品进行排名和过滤：

SELECT id, category, sales
FROM (
  SELECT p.id, 
         MAX(category) category, 
         SUM(o.quantity) sales,
         ROW_NUMBER() OVER (PARTITION BY MAX(category) ORDER BY SUM(o.quantity) DESC) rn
  FROM Products p INNER JOIN OrderItems o
  ON o.prodID = p.id
  GROUP BY p.id
) t
WHERE rn <= 3
ORDER BY id;

或者，先聚合OrderItems然后加入：

SELECT id, category, sales
FROM (
  SELECT p.id, 
         p.category, 
         o.sales,
         ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY o.sales DESC) rn
  FROM Products p 
  INNER JOIN (
    SELECT prodID, SUM(quantity) sales
    FROM OrderItems
    GROUP BY prodID
  ) o ON o.prodID = p.id
) t
WHERE rn <= 3
ORDER BY id;

请参阅演示。

推荐阅读