帮助从父子模型中的数据生成报告 [英] Help with generating a report from data in a parent-children model

问题描述

关于保存在父子模型表中的数据和我需要在其上构建的报告的问题，我需要帮助.我已经尝试搜索有关亲子问题的主题，但在我的场景中找不到任何有用的内容.

I need help with a problem regarding data saved in a parent-children model table and a report I need to build upon it. I've already tried searching for topics about parent-children issues, but I couldn't find anything useful in my scenario.

我有什么

Microsoft SQL Server 2000 数据库服务器.

A Microsoft SQL Server 2000 database server.

一个 categories 表，它有四列:category_id、category_name、father_id 和 visible;这些类别有 x 个根类别(其中 x 是可变的)，并且可以是 y 级深(其中 y是可变的)，如果一个类别是根级别，它的 father_id 为 null，否则用父类别的 id 填充.

A categories table, which has four columns: category_id, category_name, father_id and visible; the categories have x root categories (where x is variable), and could be y level deep (where y is variable), if a category is a root level one it has father_id null otherwise it's filled with the id of the father category.

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

A sales table, which has z columns, one of which is category_id, a foreign key to categories.category_id; a sale must always have a category, and it could be linked anywhere in the aforementioned y level.

我需要什么

有人问我一份报告，其中只显示根(第一级)类别，以及这些类别中的每一个或其子项的销售数量，无论多深.IE.如果根类别之一是food，它有一个名为fruit的子类别，它有一个名为apple的子类别，我需要计算属于 food 或 fruit 或 apple 的每个项目.

I've been asked a report displaying only the root (first level) categories, and the quantity of sales belongings to each of these, or their children, no matter how deep. I.e. if one of the root categories is food, which has a children category named fruit, which has a children category named apple, I need to count every item belonging to food or fruit or apple.

您不能使用嵌套集数据模型吗?

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

I know of the nested set model, but I already have the table this way, and migrating it to the nested set model would be a pain (let alone I didn't even fully grasp how nested set works), not counting the changes needed in the application using the database. (If someone thinks this is still the least pain way, please explain why and how the current data could be migrated.)

您不能使用 CTE(通用表表达式)吗?

不，它是一个 Microsoft SQL Server 2000，并且在 2005 版本中引入了公共表表达式.

No, it's a Microsoft SQL Server 2000, and Common Table Expressions are introduced in the 2005 edition.

提前致谢，安德里亚.

推荐答案

基于 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 应该可以满足您的需求

SQL 2005 Based solution

A CTE should get you what you want

• 从类别中选择每个类别作为根项目
• 递归添加每个根项的每个子项
• INNER JOIN 结果与您的销售表.由于每个 root 都在 CTE 的结果中，一个简单的 GROUP BY 就足以获得每个项目的计数.

• Select each category from Categories to be the root item
• recursively add each child of every root item
• INNER JOIN the results with your sales table. As every root is in the result of the CTE, a simple GROUP BY is sufficient to get a count for each item.

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

这篇关于帮助从父子模型中的数据生成报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！