sql-server - SQL Server：连接 CALENDAR 表和 DATA 表

问题描述

有人能告诉我为什么我不能让这些表加入吗？第一个表是Calendar仅包含日期的动态表，第二个表是包含日期/时间、行为名称（字符串）、帐户#（整数）的数据表。我需要加入这些表格，以便显示所有日期，即使该帐户当天没有任何行为。

我目前正在获取如下所示的数据：

ACCT#     Date      Behavior 1        Behavior 2
-------------------------------------------------
123456   2/3/2018      1               3
123456   2/5/2018      1               2
123456   2/10/2018     1               0
123456   2/11/2018     1               0

我希望/需要它是：

ACCT#       Date      Behavior 1        Behavior 2
----------------------------------------------------
123456      2/1/2018      0               0
123456      2/2/2018      0               0
123456      2/3/2018      1               3
123456      2/4/2018      0               3
123456      2/5/2018      1               0
123456      2/6/2018      0               0
123456      2/7/2018      0               0
123456      2/8/2018      0               0
123456      2/9/2018      0               1
123456      2/10/2018     1               0
123456      2/11/2018     1               0

这是我正在使用的查询

DECLARE @Start DATE, @End DATE, @Account# INT, @behavior varchar(50)
SET @Start = '2/1/2019';
SET @End = '3/20/2019';
SET @FSH = '123456';
SET @Behavior = 'Attack';

;WITH MonthDates AS 
(
    SELECT
        DATEADD(DAY, n1.number * 10 + n0.number, @Start) AS date
    FROM
        (SELECT 1 AS number UNION 
         SELECT 2 UNION 
         SELECT 3 UNION 
         SELECT 4 UNION 
         SELECT 5 UNION 
         SELECT 6 UNION 
         SELECT 7 UNION 
         SELECT 8 UNION 
         SELECT 9 UNION 
         SELECT 0) AS n0
    CROSS JOIN
        (SELECT 1 AS number UNION 
         SELECT 2 UNION 
         SELECT 3 UNION 
         SELECT 4 UNION 
         SELECT 5 UNION 
         SELECT 6 UNION 
         SELECT 7 UNION 
         SELECT 8 UNION 
         SELECT 9 UNION 
         SELECT 0) AS n1
    WHERE
        DATEADD(DAY, n1.number * 10 + n0.number, @Start) BETWEEN @Start AND @End
)
SELECT
    md.date,
    ACCT#,
    COUNT(CASE Behavior WHEN 'Behavior1' THEN 1 ELSE NULL END) AS Behavior1Alias,
    COUNT(CASE Behavior WHEN 'Behavior1' THEN 1 ELSE NULL END) AS Behavior2Alias
FROM
    vw_MaladaptiveDailySummary as vwMals
FULL JOIN 
    MonthDates AS md ON FORMAT(md.Date,'yyyy-MM-dd') = FORMAT(vwMals.Date,'yyyy-MM-dd')    
WHERE
    vwMals.Date >= @start AND vwMals.date <= @end 
    AND Account# = @FSH
GROUP BY
    md.date
ORDER BY
    md.Date

我已将查询更新为以下内容：

declare @Start date, @End date, @FSH int, @behavior varchar(max)
Set @Start='2/1/2018';
Set @End ='12/20/2019';
Set @FSH ='123456';
set @Behavior = 'Attack';

SELECT

    MonthDates.Date
        ,count(case Behavior when 'Behavior1' then 1 else NULL end) as BehaviorsAlias1
    ,count(case Behavior when 'Behavior2' then 1 else null end) as BehaviorAlias2


FROM

    (   

        SELECT

            Date = DateAdd( Day, n1.number * 10 + n0.number, @Start )

        FROM

            (SELECT 1 AS number UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) as n0

        CROSS JOIN

            (SELECT 1 AS number UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) as n1

        WHERE   DateAdd( Day, n1.number * 10 + n0.number,@Start ) BETWEEN @Start AND @End


    ) AS MonthDates

LEFT JOIN vw_MaladaptiveDailySummary 

    ON vw_MaladaptiveDailySummary.DATE = MonthDates.Date

GROUP BY

    MonthDates.Date

ORDER BY

    MonthDates.Date

这确实产生了我想要的一半；另一半是能够过滤 ACCT#/和/或行为。我现在得到的结果是当天所有 ACCT# 的总数。

Date      Behavior 1        Behavior 2
----------------------------------------------------
2/1/2018      5               0
2/2/2018      3               0
2/3/2018      1               3
2/4/2018      0               3
2/5/2018      1               0
2/6/2018      9               5
2/7/2018      6               0
2/8/2018      3               0
2/9/2018      0               8
2/10/2018     1               0
2/11/2018     1               6

标签： sql-server

sql-server - SQL Server：连接 CALENDAR 表和 DATA 表

问题描述

解决方案

推荐阅读