获取记录日期时间字段值作为结果列的 SQL 语句

Question

我有以下两张表

activity(activity_id, title, description, group_id)
statistic(statistic_id, activity_id, date, user_id, result)

group_id 和 user_id 来自事件目录。结果是一个整数。

给定一个 user_id 和我在业务逻辑方面计算的 6 天(周一 - 周六)的日期范围，并且该日期范围内的某些日期可能没有特定的统计结果日期(即第 1 天和第 4 天可能已输入特定事件的统计行，但第 2、3、5 和 6 天可能没有任何条目)我如何获得具有以下格式的 SQL 结果？请记住，如果特定事件在统计表中没有特定日期的记录，则该日期应在 SQL 结果中返回 0。

activity_id    group_id    day1result    day2result    day3result    day4result    day5result    day6 result
-----------    --------    ----------    ----------    ----------    ----------    ----------    -----------
sample1        Secured     0             5             1             0             2             1
sample2        Unsecured   1             0             0             4             3             2

注意:目前我正计划在业务逻辑中处理这个问题，但这需要多个查询(一个用于为该用户在日期范围内创建不同事件的列表，一个用于循环遍历每个日期的每个事件结果或缺少结果，用日期相关的结果填充数组的第二维)。对于每个日期范围内的每个用户，最终可能会有 50 多个查询，这对我来说似乎有点过分了。

我让它工作了 4 天，我可以让它工作 6 天，但这似乎有点过分了。有没有办法简化这个？:

SELECT d1d2.activity_id, ISNULL(d1d2.result1,0) AS day1, ISNULL(d1d2.result2,0) AS day2, ISNULL(d3d4.result3,0) AS day3, ISNULL(d3d4.result4,0) AS day4
FROM
    (SELECT ISNULL(d1.activity_id,0) AS activity_id, ISNULL(result1,0) AS result1, ISNULL(result2,0) AS result2
     FROM
         (SELECT ISNULL(statistic_result,0) AS result1, ISNULL(activity_id,0) AS activity_id
          FROM statistic 
              WHERE user_id='jeremiah' AND statistic_date='11/22/2011'
          ) d1
          FROM JOIN
          (SELECT ISNULL(statistic_result,0) AS result2, ISNULL(activity_id,0) AS activity_id
           FROM statistic WHERE user_id='jeremiah' AND statistic_date='11/23/2011'
          ) d2
          ON d1.activity_id=d2.activity_id
     ) d1d2
     FULL JOIN
     (SELECT d3.activity_id AS activity_id, ISNULL(d3.result3,0) AS result3, ISNULL(d4.result4,0) AS result4
      FROM
          (SELECT ISNULL(statistic_result,0) AS result3, ISNULL(activity_id,0) AS activity_id
           FROM statistic WHERE user_id='jeremiah' AND statistic_date='11/24/2011'
          ) d3
          FULL JOIN
          (SELECT ISNULL(statistic_result,0) AS result4, ISNULL(activity_id,0) AS activity_id
           FROM statistic WHERE user_id='jeremiah' AND statistic_date='11/25/2011'
          ) d4
          ON d3.activity_id=d4.activity_id
     ) d3d4
     ON d1d2.activity_id=d3d4.activity_id
ORDER BY d1d2.activity_id

Answer 1

这是处理这种事情的典型方法:

DECLARE @minDate DATETIME,
        @maxdate DATETIME,
        @userID VARCHAR(200)

SELECT  @minDate = '2011-11-15 00:00:00',
        @maxDate = '2011-11-22 23:59:59',
        @userID = 'jeremiah'

SELECT A.activity_id, A.group_id, 
        SUM(CASE WHEN DATEDIFF(day, @minDate, S.date) = 0 THEN S.Result ELSE 0 END) AS Day1Result,
        SUM(CASE WHEN DATEDIFF(day, @minDate, S.date) = 1 THEN S.Result ELSE 0 END) AS Day2Result,
        SUM(CASE WHEN DATEDIFF(day, @minDate, S.date) = 2 THEN S.Result ELSE 0 END) AS Day3Result,
        SUM(CASE WHEN DATEDIFF(day, @minDate, S.date) = 3 THEN S.Result ELSE 0 END) AS Day4Result,
        SUM(CASE WHEN DATEDIFF(day, @minDate, S.date) = 4 THEN S.Result ELSE 0 END) AS Day5Result,
        SUM(CASE WHEN DATEDIFF(day, @minDate, S.date) = 5 THEN S.Result ELSE 0 END) AS Day6Result
FROM activity A 
    LEFT OUTER JOIN statistic S 
        ON A.activity_id = S.activity_ID
            AND S.user_id = @userID
WHERE S.date between @minDate AND @maxDate 
GROUP BY A.activity_id, A.group_id 

首先，我使用 group by 将结果集减少到每个 activity_id/group_id 一行，然后我使用 CASE 为每个单独的列分隔值.在这种情况下，我正在查看过去七天中的哪一天，但您可以使用其中的任何逻辑来确定日期。如果该行是针对特定日期的，则 case 语句将返回 S.result 的值，否则返回 0。 SUM 会将各个值相加(如果只有一个，则只相加)并将其合并为一行。

您还会注意到我的日期范围是基于范围内第一天的午夜和范围内最后一天的晚上 11:59，以确保所有时间都包含在范围内。

最后，我将执行左连接，这样您的列中将始终有一个 0，即使没有统计信息也是如此。