SQL查询中的派生字段

Question

使用Server 2008 (或者可能是2012年)，我有一个带有UserID、Date和Action字段的“activity”表，我希望计算每个用户每天执行每个操作的次数。不幸的是，“action”只是一个文本字段，有时它包含的不仅仅是基本的操作。所以我想做这样的事情：

SELECT UserID,
       Cast([Timestamp] AS DATE)                         AS DateField,
       Count(Action LIKE '%Logged In%')                  AS LoggedIn,
       Count(Action LIKE '%Requested Planning Data%')    AS Planning,
       Count(Action LIKE '%Requested Time Series Data%') AS TimeSeries,
       Count(Action LIKE '%Requested Analytical Data%')  AS Analytical
FROM   Activity
ORDER  BY UserID,
          DateField 

结果应该是这样的：

UserID  DateField   LoggedIn  Planning  TimeSeries  Analytical
------  ---------   --------  --------  ----------  ----------
1       01-11-2014  1         1         0           2
1       02-11-2014  3         7         2           5
2       01-11-2014  1         8         0           0
...

但我不知道如何导出“计数”列。这有可能吗？原始表看起来如下所示：

UserID  Timestamp            Action
1       01-11-2014 09:00:15  User Logged In
1       01-11-2014 09:01:15  User Requested Planning Data for 2 sites
1       01-11-2014 09:07:15  User Requested Analytical Data for 1 sites
1       01-11-2014 09:25:15  User Requested Analytical Data for 8 sites
1       02-11-2014 09:03:15  User Logged In
1       02-11-2014 09:07:15  User Logged In
...

-阿利斯泰尔

Answer 1

您需要使用conditional aggregate。尝尝这个。

SELECT UserID, CAST([Timestamp] AS date) AS DateField,
   COUNT(CASE WHEN Action LIKE '%Logged In%' THEN 1 END) 
       AS LoggedIn,
   COUNT(CASE WHEN Action LIKE '%Requested Planning Data%' THEN 1 END) 
       AS Planning,
   COUNT(CASE WHEN Action LIKE '%Requested Time Series Data%' THEN 1 END) 
       AS TimeSeries,
   COUNT(CASE WHEN Action LIKE '%Requested Analytical Data%' THEN 1 END) 
       AS Analytical
FROM Activity
ORDER BY UserID, CAST([Timestamp] AS date)