sql - 查询以获得每个类别 + 子类别相等的行数

Question

这是我的 SQL 表结构的样子:

CREATE TABLE TempCategory
(
    Id BIGINT,
    Name VARCHAR(100)
)

CREATE TABLE TempSubCategory
(
    Id BIGINT,
    CategoryId BIGINT,
    Name VARCHAR(100)
)

CREATE TABLE TempProduct
(
    Id BIGINT,
    SubCategoryId BIGINT,
    Name VARCHAR(100)
)

http://sqlfiddle.com/#!3/2606fd/4

我正在编写一个 SSRS 报告，将类别显示为 x 轴，将子类别显示为 y 轴，将产品显示为数据。每个类别都有自己的子类别，因此我在每个行组的列组中显示子类别。

SSRS 报告不会为未获取值的行绘制单元格。所以我的报告最终看起来像这样:

这是我当前查询的样子:

SELECT  TempCategory.Id, 'MainCategoryId',
        TempCategory.Name 'CategoryName',
        TempSubCategory.id 'SubCategoryId',
        TempSubCategory.Name 'SubCategory',
        TempProduct.Id 'ProductId',
        TempProduct.Name 'ProductName'
          FROM TempCategory
    INNER JOIN TempSubCategory
        ON TempCategory.Id = TempSubCategory.CategoryId
    INNER JOIN TempProduct
        ON TempSubCategory.Id = TempProduct.SubCategoryId

我想要做的是修改查询，使其始终为每个子类别组返回相同数量的行，以便我在它确实有数据的行中有空值或 0。

例如:类别 1 有 3 个子类别，最大数量的产品在子类别 1 中，所以我希望查询返回 5(子类别 1 的最大产品数量)具有主要类别 1 的每个子类别的行。

对于类别 2，它将为每个子类别返回 2 行，因为最大数量的产品属于子类别 2。

是否可以在 SQL 中执行此操作，或者是否有其他方法可以在 SSRS 报告中执行此操作？

--更新--

这是一个包含 ProductName 行组的表

这是带有 SubCategory 列组的矩阵

这是一个包含 Product Name 行组的表格

Answer 1

是的，这是一种(迂回)方式:

with numbers as -- create lazy numbers table; feel free to replace with a proper one
(
  select distinct number
  from master..spt_values
  where number between 1 and 100
)
, rowCounts as
(
  select Category = tc.Name
    , SubCategory = tsc.Name
    , SubCategoryId = tsc.Id
    , MaxSubCatRows = count(1)
  from TempCategory tc
    inner join TempSubCategory tsc on tc.Id = tsc.CategoryId
    inner join TempProduct p on tsc.Id = p.SubCategoryId
  group by tc.Name
    , tsc.Name
    , tsc.Id
)
, maxRowCountPerGroup as
(
  select Category
    , MaxSubCatRows = max(MaxSubCatRows)
  from rowCounts
  group by Category
)
, allCats as
(
  select rc.Category
    , rc.SubCategory
    , rc.SubCategoryId
    , n.number
  from rowCounts rc
    inner join maxRowCountPerGroup mr on rc.Category = mr.Category
    cross apply (select number
                 from numbers
                 where number between 1 and mr.MaxSubCatRows) n
)
, orderedProducts as
(
  select *
    , productRowNumber = row_number() over (partition by SubCategoryId
                                            order by Id)
  from TempProduct
)
select c.Category
  , c.SubCategory
  , Product = p.Name
from allCats c
  left join orderedProducts p on c.subCategoryId = p.subCategoryId
    and c.number = p.productRowNumber
order by c.Category
  , c.SubCategory
  , case when p.Name is null then 1 else 0 end -- nulls last
  , p.Name

SQL Fiddle with demo .

所以...这是在做什么:

• 获取每个类别/子类别组合的行数
• 获取每组这些行数的最大值
• 使用数字表为每个创建 n 占位符行类别/子类别组合，其中 n 是类别的最大值从上面
• 为子类别中的每个产品分配行号
• 向左加入我们的类别占位符行到我们订购的产品

现在我们有了所需的行数，包括填充 SSRS 报告所需的 NULL 行。

剩下的就是将其应用于报表数据集；坐下来欣赏出现的额外行。