sql - 选择日期范围内的 COUNT 个

Question

我正在尝试使用 MSSQL Express 2005 获取在某个日期范围内收到的电子邮件的计数。我已经管理了这么多，但现在我想将数据拆分为已完成的操作。

这是我到目前为止所得到的:

SELECT EmailAddress, COUNT(EmailAddress) 
  FROM mails 
  WHERE ReceivedTime >= DATEADD(DAY, -7, GETDATE()) 
  GROUP BY EmailAddress
    HAVING COUNT(EmailAddress) > 10

这为我提供了上周邮寄超过 10 次的地址列表，但我们还有另一列告诉我们邮件发生了什么。我正在努力实现这样的目标:

SELECT EmailAddress, 
  COUNT(ActionTaken WHERE ActionTaken="Deleted") AS Deleted,
  COUNT(ActionTaken WHERE ActionTaken="Replied") AS Replied,
  COUNT(ActionTaken WHERE ActionTaken="Read") AS Read,
  COUNT(EmailAddress) AS Total
  FROM mails 
  WHERE ReceivedTime >= DATEADD(DAY, -7, GETDATE()) 
  GROUP BY EmailAddress
    HAVING COUNT(EmailAddress) > 10

我正在寻找这样的结果集:

 EmailAddress     | Deleted | Replied | Read | Total
 ---------------------------------------------------
 user1@domain.com | 4       | 5       | 3    | 12
 user2@domain.com | 2       | 6       | 3    | 11

我很确定这与使用 OVER(PARTITION BY) 子句有关，但我遇到了困难。我意识到 ActionTaken 字段并不理想，但我没有构建它!

Answer 1

对字符串文字使用单引号，这样无论QUOTED_IDENTIFIER如何它都可以工作。设置。

SELECT EmailAddress,
       Sum(CASE WHEN ActionTaken = 'Deleted' THEN 1 ELSE 0 END) AS Deleted,
       Sum(CASE WHEN ActionTaken = 'Replied' THEN 1 ELSE 0 END) AS Replied,
       Sum(CASE WHEN ActionTaken = 'Read' THEN 1 ELSE 0 END)    AS [Read],
       Count(EmailAddress) AS Total
FROM   mails
WHERE  ReceivedTime >= Dateadd(DAY, -7, Getdate())
GROUP  BY EmailAddress
HAVING Count(EmailAddress) > 10