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

Question

我正在使用SQLServer 2008，我迫切需要sql查询或存储过程来显示任何类别或子类别中的顶级品牌。例如，如果我通过Id分类=2，结果集应该通过执行id分类=2及其子类别和子类别的产品计数来显示电子产品中的顶级品牌。如果我通过Id分类=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

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

< li >首先获取所有类别，从根类别开始(使用递归CTE) < li >按类别计算品牌排名 < li >对于每个类别，显示前5个品牌

我还制作了一个SQLFIDLE，您可以在其中处理查询：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 

编辑：

如果需要所有选定类别（根子类别）的顶级品牌，而不是上述示例中的每个类别，则可以使用以下查询（以及此 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:(不将产品数量乘以相应类别品牌记录数量的解决方案):

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