帮助根据父子模型中的数据生成报告

Question

我需要帮助解决有关保存在父子模型表中的数据以及我需要在此基础上构建的报告的问题。我已经尝试过搜索有关亲子问题的主题，但在我的场景中找不到任何有用的内容。

我拥有

Microsoft SQL Server 2000 数据库服务器。

categories 表，有四列：category_id、category_name、father_id 和 visible >;类别有 x 个根类别（其中 x 是变量），并且深度可能是 y 级（其中 y是变量），如果一个类别是根级别类别，则它的 father_id 为 null，否则它会填充父类别的 id。

一个 sales 表，其中有 z 列，其中之一是 category_id，它是 categories.category_id 的外键;销售必须始终有一个类别，并且它可以链接到上述 y 级别的任何位置。

我需要什么

有人要求我提供一份仅显示根（第一级）类别以及每个类别或其子类别（无论深度如何）的销售财产数量的报告。即，如果根类别之一是 food，它有一个名为 fruit 的子类别，它有一个名为 apple 的子类别，我需要计算属于食物或水果或苹果的每件物品。

你不能使用嵌套集数据模型吗？

我知道嵌套集模型，但我已经有了这样的表，将其迁移到嵌套集模型会很痛苦（更不用说我什至没有完全掌握嵌套集是如何工作的），没有计算使用数据库的应用程序中所需的更改。 （如果有人认为这仍然是最不痛苦的方法，请解释为什么以及如何迁移当前数据。）

你不能使用 CTE（通用表表达式）吗？

不，它是 Microsoft SQL服务器2000和公共表表达式是在2005版本中引入的。

预先感谢，安德里亚。

Answer 1

基于 SQL 2000 的解决方案

DECLARE @Stack TABLE (
  StackID INTEGER IDENTITY
  , Category VARCHAR(20)
  , RootID INTEGER
  , ChildID INTEGER
  , Visited BIT)

INSERT INTO @Stack
SELECT  [Category] = c.category_name
        , [RootID] = c.category_id
        , [ChildID] = c.category_id
        , 0
FROM    Categories c

WHILE EXISTS (SELECT * FROM @Stack WHERE Visited = 0)
BEGIN
  DECLARE @StackID INTEGER
  SELECT  @StackID = MAX(StackID) FROM    @Stack

  INSERT INTO @Stack
  SELECT  st.Category
          , st.RootID
          , c.category_id
          , 0
  FROM    @Stack st
          INNER JOIN Categories c ON c.father_id = st.ChildID  
  WHERE   Visited = 0

  UPDATE  @Stack
  SET     Visited = 1
  WHERE   StackID <= @StackID
END

SELECT  st.RootID
        , st.Category
        , COUNT(s.sales_id)
FROM    @Stack st
        INNER JOIN Sales s ON s.category_id = st.ChildID
GROUP BY st.RootID, st.Category
ORDER BY st.RootID

基于 SQL 2005 的解决方案

CTE 应该为您提供什么您希望

• 从类别中选择每个类别作为根项目，
• 递归地将每个根项目的每个子项
• INNER JOIN添加到您的销售表中。由于每个根都在CTE的结果中，因此简单的GROUP BY足以获取每个项目的计数。

SQL语句

;WITH QtyCTE AS (
  SELECT  [Category] = c.category_name
          , [RootID] = c.category_id
          , [ChildID] = c.category_id
  FROM    Categories c
  UNION ALL 
  SELECT  cte.Category
          , cte.RootID
          , c.category_id
  FROM    QtyCTE cte
          INNER JOIN Categories c ON c.father_id = cte.ChildID
)
SELECT  cte.RootID
        , cte.Category
        , COUNT(s.sales_id)
FROM    QtyCTE cte
        INNER JOIN Sales s ON s.category_id = cte.ChildID
GROUP BY cte.RootID, cte.Category
ORDER BY cte.RootID

Answer 2

像这样的东西吗？

CREATE TABLE #SingleLevelCategoryCounts
{ 
    category_id,
    count,
    root_id
}

CREATE TABLE #ProcessedCategories
{
    category_id,
    root_id
}

CREATE TABLE #TotalTopLevelCategoryCounts
{ 
    category_id,
    count
}

INSERT INTO #SingleLevelCategoryCounts
SELECT 
    category_id, SUM(*), category_id
FROM
    Categories 
    INNER JOIN Sales ON Categories.category_id = sales.category_id
WHERE
     Categories.father_id IS NULL
GROUP BY
     Categories.category_id


WHILE EXISTS (SELECT * FROM #SingleLevelCategoryCounts)
BEGIN
   IF NOT EXISTS(SELECT * FROM #TopLevelCategoryCounts)
   BEGIN
      INSERT INTO #TopLevelCategoryCounts
      SELECT
          root_id, count
      FROM
          #SingleLevelCategoryCounts
   END 
   ELSE
   BEGIN
       UPDATE top
       SET
          top.count = top.count + level.count
       FROM
          #TopLevelCategoryCounts top
          INNER JOIN #SingleLevelCategoryCounts level ON top.category_id = level.count
   END

   INSERT INTO #ProcessedCategories
   SELECT category_id, root_id FROM #SingleLevelCategoryCounts

   DELETE #SingleLevelCategoryCounts

   INSERT INTO #SingleLevelCategoryCounts
   SELECT 
        category_id, SUM(*), pc.root_id
   FROM
        Categories 
        INNER JOIN Sales ON Categories.category_id = sales.category_id
        INNER JOIN #ProcessedCategories pc ON Categories.father_id = pc.category_id
   WHERE
         Categories.category_id NOT IN
         (
             SELECT category_id in #ProcessedCategories
         )
   GROUP BY
         Categories.category_id


END