Sql-Server-2008-R2
如何選擇多個 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