如何根据两个WHERE条件和两个计算的计数字段将SQL查询结果拆分为列？

Question

我有以下(简化的)数据库模式：

Persons:

[Id] [Name]
-------------------
1 'Peter'
2 'John'
3 'Anna'

Items:

[Id] [ItemName] [ItemStatus]
-------------------
10 'Cake' 1
20 'Dog' 2

ItemDocuments:

[Id] [ItemId] [DocumentName] [Date]
-------------------
101 10 'CakeDocument1' '2016-01-01 00:00:00'
201 20 'DogDocument1' '2016-02-02 00:00:00'
301 10 'CakeDocument2' '2016-03-03 00:00:00'
401 20 'DogDocument2' '2016-04-04 00:00:00'


DocumentProcessors:

[PersonId] [DocumentId]
-------------------
1 101
1 201
2 301

我还设置了一个SQL小提琴来玩：http://www.sqlfiddle.com/#!3/e6082

关系逻辑如下:每个人都可以处理ItemDocuments的零或无限数(多到多)；每个ItemDocument完全属于一个项目(一对多)。项目状态1-活动，2-关闭

我需要的是一份满足以下要求的报告：

• 对于person表中的每个人，显示与此人相关的ItemDocuments项的计数
• 计数应由ItemStatus拆分成两列
• 查询应该由两个可选的日期周期(在ItemDocuments.Date字段的两个条件之间使用)进行过滤，并且项计数也应该分为两个句点。
• 如果一个人没有分配任何ItemDocuments，那么它仍然应该显示在结果中，所有计数值都设置为0。
• 如果一个人对一个项目有一个以上的ItemDocument，那么该项目仍然应该只被计算一次。

本质上，如果我使用这两个句点为NULL (读取所有数据)，结果应该是这样的：

    [PersonName]    [Active Items for period 1] [Closed Items for period 1] [Active Items for period 2] [Closed Items for period 2]
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    'Peter' 1   1   1   1
    'John'  1   0   1   0
    'Anna'  0   0   0   0

虽然我可以为每个需求分别创建一个SQL查询，但我有一个问题要理解如何将它们合并为一个。

例如，我可以将ItemStatus计数拆分为两列

COUNT(CASE WHEN t.ItemStatus = 1 THEN 1 ELSE NULL END) AS Active, 
COUNT(CASE WHEN t.ItemStatus = 2 THEN 1 ELSE NULL END) AS Closed

并且我可以通过两个时间段(使用MS SQL server规范中的max/min日期常量来避免在可选期间日期上使用NULL)进行筛选。

between coalesce(@start1, '1753-01-01') and coalesce(@end1, '9999-12-31')
between coalesce(@start2, '1753-01-01') and coalesce(@end2, '9999-12-31')

但是，考虑到表之间的连接，如何将所有这些组合在一起呢？

是否有任何技术、join 或MS特定的方法可以有效地做到这一点？

我的第一次尝试似乎是按要求工作的，但它似乎多次重复了难看的子查询复制：

DECLARE @start1 DATETIME, @start2 DATETIME, @end1 DATETIME, @end2 DATETIME

-- SET @start2 = '2017-01-01'

SELECT 
p.Name,
(SELECT COUNT(1)
    FROM Items i
    WHERE i.ItemStatus = 1 AND EXISTS(
        SELECT 1 
        FROM DocumentProcessors AS dcp
        INNER JOIN ItemDocuments AS idc ON dcp.DocumentId = idc.Id
        WHERE dcp.PersonId = p.Id AND idc.ItemId = i.Id
        AND idc.Date BETWEEN COALESCE(@start1, '1753-01-01') AND COALESCE(@end1, '9999-12-31')
        )
) AS Active1,

(SELECT COUNT(*)
    FROM Items i
    WHERE i.ItemStatus = 2 AND EXISTS(
        SELECT 1 
        FROM DocumentProcessors AS dcp
        INNER JOIN ItemDocuments AS idc ON dcp.DocumentId = idc.Id
        WHERE dcp.PersonId = p.Id AND idc.ItemId = i.Id
        AND idc.Date BETWEEN COALESCE(@start1, '1753-01-01') AND COALESCE(@end1, '9999-12-31')
        )
    ) AS Closed1,

(SELECT COUNT(1)
    FROM Items i
    WHERE i.ItemStatus = 1 AND EXISTS(
        SELECT 1 
        FROM DocumentProcessors AS dcp
        INNER JOIN ItemDocuments AS idc ON dcp.DocumentId = idc.Id
        WHERE dcp.PersonId = p.Id AND idc.ItemId = i.Id
        AND idc.Date BETWEEN COALESCE(@start2, '1753-01-01') AND COALESCE(@end2, '9999-12-31')
        )
) AS Active2,

(SELECT COUNT(*)
    FROM Items i
    WHERE i.ItemStatus = 2 AND EXISTS(
        SELECT 1 
        FROM DocumentProcessors AS dcp
        INNER JOIN ItemDocuments AS idc ON dcp.DocumentId = idc.Id
        WHERE dcp.PersonId = p.Id AND idc.ItemId = i.Id
        AND idc.Date BETWEEN COALESCE(@start2, '1753-01-01') AND COALESCE(@end2, '9999-12-31')
        )
    ) AS Closed2

FROM Persons p

Answer 1

我不确定我是否真的得到了你想要的，但你可以试试这个

WITH AllData AS
(
    SELECT p.Id AS PersonId
          ,p.Name AS Person
          ,id.Date AS DocDate
          ,id.DocumentName AS DocName
          ,i.ItemName AS ItemName
          ,i.ItemStatus AS ItemStatus
          ,CASE WHEN id.Date BETWEEN COALESCE(@start1, '1753-01-01') AND COALESCE(@end1, '9999-12-31') THEN 1 ELSE 0 END AS InPeriod1  
          ,CASE WHEN id.Date BETWEEN COALESCE(@start2, '1753-01-01') AND COALESCE(@end2, '9999-12-31') THEN 1 ELSE 0 END AS InPeriod2
    FROM Persons AS p
    LEFT JOIN DocumentProcessors AS dp ON p.Id=dp.PersonId
    LEFT JOIN ItemDocuments AS id ON dp.DocumentId=id.Id
    LEFT JOIN Items AS i ON id.ItemId=i.Id
)
SELECT PersonID 
      ,Person
      ,COUNT(CASE WHEN ItemStatus = 1 AND InPeriod1 = 1 THEN 1 ELSE NULL END) AS ActiveIn1
      ,COUNT(CASE WHEN ItemStatus = 2 AND InPeriod1 = 1  THEN 1 ELSE NULL END) AS ClosedIn1
      ,COUNT(CASE WHEN ItemStatus = 1 AND InPeriod2 = 1 THEN 1 ELSE NULL END) AS ActiveIn2
      ,COUNT(CASE WHEN ItemStatus = 2 AND InPeriod2 = 1  THEN 1 ELSE NULL END) AS ClosedIn2
FROM AllData
GROUP BY PersonID,Person