主页 / dba / 问题 / 6668
Accepted
Peter Bridger
Asked: 2011-10-11 06:54:37 +0800 CST

使用多个 PIVOT JOIN 和 GROUP BY 进行高级报告生成

  • 772

我开发了一个时间表记录系统，允许用户记录：

  • 一段时间（9:00 到 12:00）
  • 他们在那段时间做了什么（DutyActivity：1小时报告撰写，2小时培训）

当他们提交时间表时，系统还会记录他们的 GeographyId、RoleId、CategoryId（单个 id）和 AttributeId（多个 id）——这是用户在该时间点的快照。

然后可以查询此记录的时间表信息以获取报告。以最简单的形式，系统将返回一个 UserIds 列表，其中包含PeriodMinutes每个记录的 SUM DutyActivityId。如下所示：

简单的结果

但是，我还想做的是分组并返回AttributeIds与每个时间表一起记录的记录（在TimesheetAttribute表格中）。但是，因为每个时间表都有多个AttributeIds记录，所以它有点复杂。

我的尝试创建了以下报告：

复杂的结果

然而，这个问题是for SUM(PeriodMinutes)eachDutyCategoryId返回的现在太高了。似乎我AttributeIds为每个时间表包含的 SQL 导致SUM计算不正确。

我创建了一个 ZIP，其中包含用于数据库架构和数据的 SQL，以及我在此处截屏的两个查询的 SQL：ZIP Timesheet 示例数据库

以下信息包含在 ZIP 中，但为了便于访问，我将它们链接在这里：

数据库图

报告时间表，没有属性分组

SELECT
*
FROM
(
SELECT
T.UserId,
T.RoleId,T.GeographyId,T.CategoryId,
TDA.DutyActivityId AS TypeId,
SUM(TDA.PeriodMinutes) AS Total
FROM 
Timesheet AS T

LEFT JOIN 
TimesheetDutyActivity AS TDA ON
TDA.TimesheetId = T.TimesheetId

GROUP BY
T.UserId,
T.RoleId,T.GeographyId,T.CategoryId,
TDA.DutyActivityId
) AS SourceTable

/* PIVOT against the known DutyActivityIds */
PIVOT
(
SUM(Total)
FOR TypeId IN ([1],[2],[3],[4],[5])
)
AS PivotType

ORDER BY UserId ASC

报告时间表，尝试进行属性分组

SELECT
*
FROM
(
SELECT
T.UserId,
T.RoleId,T.GeographyId,T.CategoryId,
TA.AttributeId * -1 AS AttributeId, /* Multiply AttributeId by -1 in order to create negative Ids, so that two PIVOT operations can be used */
TDA.DutyActivityId AS TypeId,
SUM(TDA.PeriodMinutes) AS Total

FROM 
Timesheet AS T

LEFT JOIN 
TimesheetAttribute AS TA ON
TA.TimesheetId = T.TimesheetId

LEFT JOIN 
TimesheetDutyActivity AS TDA ON
TDA.TimesheetId = T.TimesheetId

GROUP BY
T.UserId,
AttributeId,
T.RoleId,T.GeographyId,T.CategoryId,
TDA.DutyActivityId
) AS SourceTable

/* PIVOT against the known DutyActivityIds */
PIVOT
(
SUM(Total)
FOR TypeId IN ([1],[2],[3],[4],[5])
)
AS PivotType

/* Also PIVOT against the known AttributeIds */
PIVOT
(
SUM(AttributeId)
FOR AttributeId IN ([-1],[-2],[-3],[-4],[-5])
)
AS PivotAttribute

ORDER BY UserId ASC
join pivot

1 个回答

  1. Best Answer

    我设法通过删除第二个 PIVOT 和AttributeIdGROUP BY 参数并在属性上使用 LEFT JOIN 来获得所需的结果。

    此答案由visahk16在SQL 团队论坛上提供：

    SELECT
    *
    FROM
    (
    SELECT
    T.UserId,
    T.RoleId,T.GeographyId,T.CategoryId,
    TA.[-1],TA.[-2],TA.[-3],TA.[-4],TA.[-5],
    TDA.DutyActivityId AS TypeId,
    SUM(TDA.PeriodMinutes) AS Total

    FROM 
    Timesheet AS T

    LEFT JOIN 
    (SELECT TimesheetId,
     SUM(CASE WHEN AttributeId ='1' THEN -1 ELSE 0 END) AS [-1],
     SUM(CASE WHEN AttributeId ='2' THEN -1 ELSE 0 END) AS [-2],
...
     SUM(CASE WHEN AttributeId ='5' THEN -1 ELSE 0 END) AS [-5] 
     FROM TimesheetAttribute 
     GROUP BY TimesheetId 
     )AS TA ON
    TA.TimesheetId = T.TimesheetId

    LEFT JOIN 
    TimesheetDutyActivity AS TDA ON
    TDA.TimesheetId = T.TimesheetId

    GROUP BY
    T.UserId,
    AttributeId,
    T.RoleId,T.GeographyId,T.CategoryId,
    TDA.DutyActivityId
    ) AS SourceTable

    /* PIVOT against the known DutyActivityIds */
    PIVOT
    (
    SUM(Total)
    FOR TypeId IN ([1],[2],[3],[4],[5])
    )
    AS PivotType

    ORDER BY UserId ASC
    • 1

相关问题