sql - 任何类别或子类别中的顶级品牌列表

Question

我正在使用 SQL Server 2008，我迫切需要 sql 查询或存储过程来显示任何类别或子类别中的顶级品牌。例如，如果我通过 IdCategory=2，结果集应该通过在 idCategory=2 及其子类别和子子类别上执行产品计数来显示电子产品中的顶级品牌。如果我通过 IdCategory=38，结果应该显示电话和移动设备以及移动电话中的顶级品牌。我希望我能够明确我的要求。

这是我数据库中的表。

分类

IdCategory    CategoryName              ParentCategoryId
---------------------------------------------------------
1             Appliances                Null
2             Electronics               Null
38            Phones & Mobile Devices   2
39            Cameras & Photography     2
115           Mobile Phones             38
121           Digital Cameras           39

品牌

IdBrand       BrandName 
------------------------
1             Nokia
2             Samsung
3             Canon

下表打破了类别和品牌表之间的多对多关系

分类品牌

IdCategoriesBrand    IdCategory   IdBrand
-----------------------------------------
1                    2            1
2                    38           1
3                    115          1
4                    2            2
5                    38           2
6                    115          2

产品展示

IdProduct     Product Name       IdCategory    IdBrand
---------------------------------------------------------
1             AAAA               115           1
2             BBBB               115           2
3             CCCC               121           3
4             DDDD               115           1
5             EEEE               121           3

关于产品表的假设

产品只能添加到第三级子类别(例如手机、数码相机)

这是创建表的脚本

CREATE TABLE [dbo].[Categories](
    [IdCategory] [bigint]  NOT NULL,
    [CategoryName] [nvarchar](50) NULL,  
    [ParentCategoryId] [bigint] NULL
    CONSTRAINT [PK_Categories_IdCategory] PRIMARY KEY CLUSTERED (   [IdCategory] ASC )
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[Brands](
    [IdBrand] [bigint]  NOT NULL,
    [BrandName] [nvarchar](50) NULL
    CONSTRAINT [PK_Brands_IdBrand] PRIMARY KEY CLUSTERED (  [IdBrand] ASC )
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[CategoriesBrands](
    [IdCategoriesBrand] [bigint]  NOT NULL,
    [IdCategory] [bigint] NULL,
    [IdBrand] [bigint] NULL,
    CONSTRAINT [PK_CategoriesBrands] PRIMARY KEY CLUSTERED (    [IdCategoriesBrand] ASC )
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CategoriesBrands]  WITH CHECK ADD  CONSTRAINT [FK_CategoriesBrands_Brands] FOREIGN KEY([IdBrand])
REFERENCES [dbo].[Brands] ([IdBrand])
GO

ALTER TABLE [dbo].[CategoriesBrands] CHECK CONSTRAINT [FK_CategoriesBrands_Brands]
GO

ALTER TABLE [dbo].[CategoriesBrands]  WITH CHECK ADD  CONSTRAINT [FK_CategoriesBrands_Categories] FOREIGN KEY([IdCategory])
REFERENCES [dbo].[Categories] ([IdCategory])
GO

ALTER TABLE [dbo].[CategoriesBrands] CHECK CONSTRAINT [FK_CategoriesBrands_Categories]
GO

CREATE TABLE [dbo].[Products](
    [IdProduct] [bigint]  NOT NULL,
    [ProductName] [nvarchar](200) NULL,
    [IdCategory] [bigint] NULL,
    [IdBrand] [bigint] NULL 
    CONSTRAINT [PK_Products_IdProduct] PRIMARY KEY CLUSTERED (  [IdProduct] ASC )
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_Brands] FOREIGN KEY([IdBrand])
REFERENCES [dbo].[Brands] ([IdBrand])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Brands]
GO

ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_Categories] FOREIGN KEY([IdCategory])
REFERENCES [dbo].[Categories] ([IdCategory])
GO

ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Categories]
GO

以下是在表中插入一些示例数据的脚本

INSERT INTO Categories (IdCategory, CategoryName, ParentCategoryId) VALUES(1, 'Appliances', NULL)
INSERT INTO Categories (IdCategory, CategoryName, ParentCategoryId) VALUES(2, 'Electronics', NULL)
INSERT INTO Categories (IdCategory, CategoryName, ParentCategoryId) VALUES(38, 'Phones & Mobile Devices', 2)
INSERT INTO Categories (IdCategory, CategoryName, ParentCategoryId) VALUES(39, 'Cameras & Photography', 2)
INSERT INTO Categories (IdCategory, CategoryName, ParentCategoryId) VALUES(115, 'Mobile Phones', 38)
INSERT INTO Categories (IdCategory, CategoryName, ParentCategoryId) VALUES(121, 'Digital Cameras', 39)


INSERT INTO Brands (IdBrand, BrandName) VALUES(1, 'Nokia')
INSERT INTO Brands (IdBrand, BrandName) VALUES(2, 'Samsung')
INSERT INTO Brands (IdBrand, BrandName) VALUES(3, 'Canon')


INSERT INTO CategoriesBrands (IdCategoriesBrand, IdCategory, IdBrand) VALUES(1, 2, 1)
INSERT INTO CategoriesBrands (IdCategoriesBrand, IdCategory, IdBrand) VALUES(2, 38, 1)
INSERT INTO CategoriesBrands (IdCategoriesBrand, IdCategory, IdBrand) VALUES(3, 115, 1)
INSERT INTO CategoriesBrands (IdCategoriesBrand, IdCategory, IdBrand) VALUES(4, 2, 2)
INSERT INTO CategoriesBrands (IdCategoriesBrand, IdCategory, IdBrand) VALUES(5, 38, 2)
INSERT INTO CategoriesBrands (IdCategoriesBrand, IdCategory, IdBrand) VALUES(6, 115, 2)


INSERT INTO Products (IdProduct, ProductName, IdCategory, IdBrand) VALUES(1, 'AAAA', 115, 1)
INSERT INTO Products (IdProduct, ProductName, IdCategory, IdBrand) VALUES(2, 'BBBB', 115, 2)
INSERT INTO Products (IdProduct, ProductName, IdCategory, IdBrand) VALUES(3, 'CCCC', 121, 3)
INSERT INTO Products (IdProduct, ProductName, IdCategory, IdBrand) VALUES(4, 'DDDD', 115, 1)
INSERT INTO Products (IdProduct, ProductName, IdCategory, IdBrand) VALUES(5, 'EEEE', 121, 3)

到目前为止，我已经尝试过这个，它给了我没有类别的前 5 个品牌

SELECT TOP 5
    b1.IdBrand, 
    ISNULL(b1.BrandName, '') AS BrandName, 
    count(p.IdProduct) AS 'ProductsCount'
FROM Brands b1
LEFT OUTER JOIN Products p
ON b1.IdBrand = p.IdBrand 
GROUP BY b1.IdBrand, b1.BrandName 
ORDER BY ProductsCount DESC

Answer 1

这是我解决这个问题的尝试:

首先获取所有类别，从根类别开始(使用递归 CTE)

按 categoryId 计算品牌排名

对于每个类别，显示前 5 个品牌

我还制作了一个 SQLFiddle，您可以在其中使用查询: http://sqlfiddle.com/#!3/8593b/12

这是代码:

declare @IdCategory bigint

set @IdCategory = 2

declare @selectedCategories
  table (IdCategory bigint primary key)


-- Use a recursive CTE to determine all
-- Categories that derive from current
-- Category
;with SelectedCategories as (
  select @IdCategory as IdCategory
  union all
  select c.IdCategory 
    from Categories c
    join SelectedCategories sc
      on sc.IdCategory = c.ParentCategoryId
)
-- Save Selected Categories
-- in a memory table
insert @selectedCategories (IdCategory)
select IdCategory from SelectedCategories

-- use another CTE to select the 
-- Brands in the Selected Categories
-- and compute their Category rank
-- using the RANK()
;with BrandsPerCategory as (
  select 
    c.IdCategory,
    cc.CategoryName,
    b.BrandName,
    rank() over (
      partition by c.IdCategory
      order by count(p.IdProduct) desc
    ) as BrandRank
  from @selectedCategories c
  join Categories cc
    on c.IdCategory = cc.IdCategory
  join CategoriesBrands cb
    on cb.IdCategory = c.IdCategory
  join Brands b
    on cb.IdBrand = b.IdBrand
  join Products p
    on p.IdBrand = b.IdBrand
  group by 
    c.IdCategory,
    cc.CategoryName,
    b.BrandName
)
select * 
from BrandsPerCategory
where BrandRank < 5
order by IdCategory, BrandRank 

编辑:

如果您想要所有选定类别(根+子类别)的顶级品牌，而不是上面示例中的每个类别，那么您可以使用以下查询(和 this SqlFiddle):

declare @IdCategory bigint

set @IdCategory = 2

-- Use a recursive CTE to determine all
-- Categories that derive from current
-- Category
;with SelectedCategories as (
  select @IdCategory as IdCategory
  union all
  select c.IdCategory 
    from Categories c
    join SelectedCategories sc
      on sc.IdCategory = c.ParentCategoryId
)
select top 5
  b.IdBrand, 
  b.BrandName,
  count(p.IdProduct) AS 'ProductsCount'
from SelectedCategories c
join Categories cc
  on c.IdCategory = cc.IdCategory
join CategoriesBrands cb
  on cb.IdCategory = c.IdCategory
join Brands b
  on cb.IdBrand = b.IdBrand
join Products p
  on p.IdBrand = b.IdBrand
GROUP BY b.IdBrand, b.BrandName 
ORDER BY ProductsCount DESC

编辑 2: (产品数量不乘以对应 CategoriesBrands 记录数量的解决方案):

SQLFiddle: http://sqlfiddle.com/#!3/26d60/8

代码:

;with SelectedCategories as (
  select @IdCategory as IdCategory
  union all
  select c.IdCategory 
    from Categories c
    join SelectedCategories sc
      on sc.IdCategory = c.ParentCategoryId
)
select top 5
  b.IdBrand, 
  b.BrandName,
  count(p.IdProduct) AS 'ProductsCount'
from Brands b
join Products p
  on p.IdBrand = b.IdBrand
where b.IdBrand in (
  select cb.IdBrand
  from SelectedCategories c
  join CategoriesBrands cb
    on cb.IdCategory = c.IdCategory
 )
GROUP BY b.IdBrand, b.BrandName 
ORDER BY ProductsCount DESC