SQL - 在 COUNT 中指定条件/在一个 SELECT 中多次从同一字段返回值

Question

SELECT u.USER_NAME AS [User]
    ,COUNT(ad.ACTIVITY_ID) AS [Total Activities Late]
FROM USERS u
INNER JOIN ACTIVITY_DATA ad ON u.USER_ID = ad.USER_ID
WHERE ad.DAYS_LATE > 0
    AND ad.COMPLETED_DATE IS NULL
GROUP BY u.USER_NAME

SELECT u.USER_NAME AS [User]
    ,COUNT(ad.ACTIVITY_ID) AS [Upto One Week Late]
FROM USERS u
INNER JOIN ACTIVITY_DATA ad ON u.USER_ID = ad.USER_ID
WHERE ad.DAYS_LATE BETWEEN 1 AND 7
    AND ad.COMPLETED_DATE IS NULL
GROUP BY u.USER_NAME

这两个选择都工作正常，但是我想在同一个 SELECT 语句中返回迟到的事件总数和迟到一周的事件总数。这可能吗？

我正在使用 SQL Server 2012。

Answer 1

可以，您只需要使用 SUM 和 CASE 语句即可。应该这样做:

SELECT u.USER_NAME As [User]
  , COUNT(ad.ACTIVITY_ID) As [Total Activities Late]
  , SUM(CASE WHEN ad.DAYS_LATE BETWEEN 1 and 7 THEN 1 ELSE 0 END) As [Upto One Week Late]
  FROM USERS u
  JOIN ACTIVITY_DATA ad
    ON u.USER_ID = ad.USER_ID
  WHERE ad.DAYS_LATE > 0 AND ad.COMPLETED_DATE is NULL
  GROUP BY u.USER_NAME