属于层次结构内部对象的对象的总和 [英] Aggregative sum of objects belonging to objects residing inside hierarchy structure

问题描述

我的问题在某种程度上类似于此人，但根据我的理解却足够不同.

My problem is similar in a way to this one, yet different enough in my understanding.

我有三个表:

1. Units ([UnitID] int, [UnitParentID] int)
2. Students ([StudentID] int, [UnitID] int)
3. Events ([EventID] int, [EventTypeID] int, [StudentID] int)

1. Units ([UnitID] int, [UnitParentID] int)
2. Students ([StudentID] int, [UnitID] int)
3. Events ([EventID] int, [EventTypeID] int, [StudentID] int)

Students属于单位，单位按层次结构堆叠(树形-每个孩子一个父母)，每个学生可以有不同类型的事件.

Students belong to units, units are stacked in a hierarchy (tree form - one parent per child), and each student can have events of different types.

我需要总结每个用户的每种类型的事件数，然后为一个单位中的所有用户汇总，然后按层次结构汇总，直到到达所有单位的父级为止.

I need to sum up the number of events of each type per user, then aggregate for all users in a unit, then aggregate through hierarchy until I reach the mother of all units.

结果应该是这样的:

The result should be something like this:

我的工具是SQL Server 2008和Report Builder 3. 我放了一个 SQL小提琴和示例数据，很有趣.

My tools are SQL Server 2008 and Report Builder 3. I put up a SQL fiddle with sample data for fun.

推荐答案

使用此查询:

;WITH CTE(Id, ParentId, cLevel, Title, ord) AS (
    SELECT 
        u.UnitID, u.UnitParentID, 1, 
        CAST('Unit ' + CAST(ROW_NUMBER() OVER (ORDER BY u.UnitID) AS varchar(3)) AS varchar(max)),
        CAST(RIGHT('000' + CAST(ROW_NUMBER() OVER (ORDER BY u.UnitID) AS varchar(3)), 3) AS varchar(max))
    FROM 
        dbo.Units u
    WHERE 
        u.UnitParentID IS NULL
    UNION ALL
    SELECT
        u.UnitID, u.UnitParentID, c.cLevel + 1, 
        c.Title + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY c.cLevel ORDER BY c.Id) AS varchar(3)),
        c.ord + RIGHT('000' + CAST(ROW_NUMBER() OVER (ORDER BY u.UnitID) AS varchar(3)), 3)
    FROM
        dbo.Units u
        JOIN
        CTE c ON c.Id = u.UnitParentID
    WHERE 
        u.UnitParentID IS NOT NULL
), Units AS (
SELECT 
    u.Id, u.ParentId, u.cLevel, u.Title, u.ord,
    SUM(CASE WHEN e.EventTypeId = 1 THEN 1 ELSE 0 END) AS EventA,
    SUM(CASE WHEN e.EventTypeId = 2 THEN 1 ELSE 0 END) AS EventB,
    SUM(CASE WHEN e.EventTypeId = 3 THEN 1 ELSE 0 END) AS EventC,
    SUM(CASE WHEN e.EventTypeId = 4 THEN 1 ELSE 0 END) AS EventD
FROM 
    CTE u
    LEFT JOIN
    dbo.Students s ON u.Id = s.UnitId
    LEFT JOIN
    dbo.[Events] e ON s.StudentId = e.StudentId
GROUP BY
    u.Id, u.ParentId, u.cLevel, u.Title, u.ord
), addStudents AS (
SELECT *
FROM Units
UNION ALL
SELECT 
    s.StudentId, u.Id, u.cLevel + 1,
    'Student ' + CAST(s.StudentId AS varchar(3)),
    u.ord + RIGHT('000' + CAST(s.StudentId AS varchar(3)), 0),
    SUM(CASE WHEN e.EventTypeId = 1 THEN 1 ELSE 0 END),
    SUM(CASE WHEN e.EventTypeId = 2 THEN 1 ELSE 0 END),
    SUM(CASE WHEN e.EventTypeId = 3 THEN 1 ELSE 0 END),
    SUM(CASE WHEN e.EventTypeId = 4 THEN 1 ELSE 0 END)
FROM Units u
    JOIN
    dbo.Students s ON u.Id = s.UnitId
    LEFT JOIN
    dbo.[Events] e ON s.StudentId = e.StudentId
GROUP BY
    s.StudentID, u.ID, u.cLevel, u.ord
)
SELECT --TOP(10)
    REPLICATE(' ', cLevel) + Title As Title, 
    EventA, EventB, EventC, EventD
FROM
    addStudents 
ORDER BY
    ord

为此:

Title            | EventA | EventB  | EventC | EventD
-----------------+--------+---------+--------+--------
 Unit 1          | 0      | 1       | 0      | 0
  Student 6      | 0      | 1       | 0      | 0
  Unit 1.1       | 0      | 0       | 0      | 1
   Student 21    | 0      | 0       | 0      | 1
   Student 33    | 0      | 0       | 0      | 0
   Unit 1.1.1    | 0      | 0       | 0      | 0
    Student 23   | 0      | 0       | 0      | 0
    Unit 1.1.1.1 | 3      | 2       | 3      | 0
     Student 10  | 0      | 0       | 0      | 0
     Student 17  | 1      | 0       | 0      | 0
...

SQL Fiddle Demo

这篇关于属于层次结构内部对象的对象的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！