连接压延机台

Question

我有一个数据表和一个宪兵表。如果没有这一天的数据，我在这个表上做一个连接以获得连续的日期。

我现在的问题是，有几个身份证，我需要一个计数，每一个每天。问题是，如果没有此日期的数据，我将得到id字段的“null”值。另外，下面的查询只在表中有一个id时才有效，如果我添加了第二个id，则空行不会显示，因为它们存在于另一个id中。

我的样本表是：

    --Datatable
    CREATE TABLE [dbo].[datatable](
        [the_id] int,
        [the_date] date,
        [the_count] int NULL
    ) ON [PRIMARY]

    INSERT INTO datatable (the_id, the_date, the_count) VALUES
    (1, dateadd(d, -5, getdate()), 37),
    (1, dateadd(d, -5, getdate()), 30),
    (1, dateadd(d, -5, getdate()), 70),
    (1, dateadd(d, -4, getdate()), 8),
    (1, dateadd(d, -4, getdate()), 9),
    (1, dateadd(d, -2, getdate()), 19),
    (1, dateadd(d, -2, getdate()), 3),
    (1, dateadd(d, -1, getdate()), 20)

    INSERT INTO datatable (the_id, the_date, the_count) VALUES
    (2, dateadd(d, -5, getdate()), 27),
    (2, dateadd(d, -5, getdate()), 17),
    (2, dateadd(d, -5, getdate()), 37),
    (2, dateadd(d, -3, getdate()), 8),
    (2, dateadd(d, -3, getdate()), 89),
    (2, dateadd(d, -2, getdate()), 19),
    (2, dateadd(d, -2, getdate()), 9),
    (2, dateadd(d, -1, getdate()), 20),
    (2, dateadd(d, -1, getdate()), 2)

    --Calendartable
    CREATE TABLE [dbo].[calendartable](
        [the_day] date
    )

    DECLARE @StartDate date
    DECLARE @EndDate date
    SET @StartDate = DATEADD(d, -10, GETDATE())
    SET @EndDate = DATEADD(d, 10, GETDATE())

    WHILE @StartDate <= @EndDate
    BEGIN
    INSERT INTO [calendartable] (the_day)
    SELECT @StartDate
    SET @StartDate = DATEADD(dd, 1, @StartDate)
    END

我的问题是：

    --Query
    DECLARE @mindate date
    DECLARE @maxdate date
    SELECT @mindate = MIN(CONVERT(date, the_date)),
    @maxdate = MAX(CONVERT(date, the_date))
    FROM datatable

    SELECT  dt.the_id, isnull(dt.the_date, ct.the_day),
    (SELECT SUM(the_count) WHERE the_id = dt.the_id and the_date = dt.the_date)
    as the_sum_count
    FROM calendartable AS ct
    LEFT JOIN datatable AS dt
    ON dt.the_date = ct.the_day
    AND ct.the_day BETWEEN @mindate AND @maxdate
    WHERE ct.the_day BETWEEN @mindate AND @maxdate
    GROUP BY dt.the_id, dt.the_date, ct.the_day
    ORDER BY dt.the_id, dt.the_date ASC

此查询显示当天没有数据的行，但我还需要此日期所属的id。如果datatable中有第二个id，则查询将不显示空行。

请帮帮我！

编辑：

为了更明确地显示我想要的输出：

    ID  the_date    the_count
    1   2013-03-08  137
    1   2013-03-09  17
    1   2013-03-10  null  <--- this is missing in the above query
    1   2013-03-11  22
    1   2013-03-12  20
    2   2013-03-08  81
    2   2013-03-09  null <--- this is also missing
    2   2013-03-10  97
    2   2013-03-11  28
    2   2013-03-12  22

我希望我的问题现在更清楚了！

Answer 1

我认为您希望(在1测试)：

SELECT  the_id    = dd.the_id, 
        the_day   = ct.the_day,
        the_count = COALESCE(SUM(dt.the_count), 0)
FROM 
        calendartable AS ct
    CROSS JOIN
        ( SELECT DISTINCT the_id 
          FROM datatable
          WHERE the_date BETWEEN @mindate 
                             AND @maxdate
        ) AS dd
    LEFT JOIN 
        datatable AS dt
            ON  dt.the_date = ct.the_day
            AND dt.the_id = dd.the_id 
WHERE 
        ct.the_day BETWEEN @mindate 
                        AND @maxdate
GROUP BY 
        dd.the_id, ct.the_day
ORDER BY 
        the_id, the_day ;

它也可以这样编写(2)：

SELECT  the_id    = dd.the_id, 
        the_day   = ct.the_day,
        the_count = COALESCE(
            ( SELECT SUM(dt.the_count)
              FROM   datatable AS dt
              WHERE  dt.the_date = ct.the_day
                AND  dt.the_id = dd.the_id     
            )               
                            , 0)
FROM 
        calendartable AS ct
    CROSS JOIN
        ( SELECT DISTINCT the_id 
          FROM datatable
          WHERE the_date BETWEEN @mindate 
                             AND @maxdate
        ) AS dd
WHERE 
        ct.the_day BETWEEN @mindate 
                        AND @maxdate
ORDER BY 
        the_id, the_day ;