如何選擇多個 COUNT(*) 列，每個 GROUP BY 具有不同的 WHERE 子句？

我有以下架構：

CREATE TABLE Person (
 PersonId int PRIMARY KEY
)

CREATE TABLE Action (
 ActionId int PRIMARY KEY,
 PersonId int NOT NULL FOREIGN KEY REFERENCES Person(PersonId),
 ActionTime datetime NOT NULL
)

以及以下數據：

INSERT INTO Person (PersonId) VALUES
(1),
(2),
(3),
(4)

INSERT INTO Action (ActionId, PersonId, ActionTime) VALUES
(1, 1, '2014-02-01'),
(2, 1, '2014-02-02'),
(3, 2, '2014-02-02'),
(4, 3, '2014-03-05')

我想執行一個查詢，顯示每個人在每月 15 日之間執行的操作數。例如，我正在嘗試以下操作：

SELECT
   Person.PersonId,
   COALESCE(GroupA_Actions_Made, 0) AS GroupA_Actions_Made,
   COALESCE(GroupB_Actions_Made, 0) AS GroupB_Actions_Made
FROM
   Person
   RIGHT OUTER JOIN (
       SELECT
           PersonId,
           COUNT(*) AS GroupA_Actions_Made
       FROM
           Action
       WHERE
           ActionTime BETWEEN '2014-01-15 00:00:00' AND '2014-02-14 23:59:59'
       GROUP BY
           PersonId
   ) GroupA ON GroupA.PersonId = Person.PersonId
   RIGHT OUTER JOIN (
       SELECT
           PersonId,
           COUNT(*) AS GroupB_Actions_Made
       FROM
           Action
       WHERE
           ActionTime BETWEEN '2014-02-15 00:00:00' AND '2014-03-14 23:59:59'
       GROUP BY
           PersonId
   ) GroupB ON GroupB.PersonId = Person.PersonId

但是，我正在嘗試的查詢返回以下內容：

PersonId | GroupA_Actions_Made | GroupB_Actions_Made
(null)     0                     1

但我想

PersonId | GroupA_Actions_Made | GroupB_Actions_Made
1          2                     0
2          1                     0
3          0                     1

（我不希望結果為沒有採取行動的人返回任何東西。）

如何獲得所需格式的結果？

更新

除了我必須將它們包裝如下之外，每個答案都有效：

SELECT
   PersonId,
   GroupA_Actions_Made,
   GroupB_Actions_Made
FROM (
   -- (answer)
) t
WHERE
   GroupA_Actions_Made > 0
   OR GroupB_Actions_Made > 0

使用 SQL Server Profiler，接受的答案似乎在大型數據集上具有最快的查詢時間。

這會給你你想要的結果，但我不確定它是否是最靈活的一段程式碼。

SELECT p.PersonId,
   SUM(CASE 
         WHEN a.ActionTime >= '2014-01-15 00:00:00' 
           AND a.ActionTime < '2014-02-15 00:00:00'
         THEN 1 
         ELSE 0 
       END) AS GroupA_Actions_Made,
   SUM(CASE 
         WHEN a.ActionTime >= '2014-02-15 00:00:00' 
           AND a.ActionTime < '2014-03-15 00:00:00'
         THEN 1 
         ELSE 0 
       END) AS GroupB_Actions_Made
FROM
   Person p
JOIN
   Action a on p.PersonId = a.PersonId
GROUP BY p.PersonId

這應該更靈活一些。

SELECT PersonId, [2014.02] AS GroupA_Actions_Made, [2014.03] AS GroupB_Actions_Made
FROM (
   SELECT ActionId, PersonId, 
       CONVERT(char(7), DATEADD(month,1,DATEADD(dd,-14,ActionTime)), 102) Grouping
   FROM Action
   ) p
PIVOT (COUNT(ActionId) 
   FOR Grouping IN ([2014.02],[2014.03])
   ) AS pvt

引用自：https://dba.stackexchange.com/questions/65416