在 SQL 中显示每个日历周一行

Question

我有一个 SQL 查询，可以按商品、按周提取单位销售额：

SELECT sls_vendor, 
   sls_item, 
   sls_units, 
   DATEPART(week, sls_week) AS sls_date 
FROM   mytable 

假设我正在查看 8 周的时间段，但并非每个商品/供应商组合都有完整 8 周的销售额。但是我需要我的查询在该实例中显示空值。因此，无论是否存在，查询都会为每个项目/供应商组合返回 8 行。

我尝试创建一个包含数字 28 到 35 的临时表，并对上面的查询执行左连接，但没有显示空值。结果与单独运行原始查询没有什么不同。

我可以想到如何使用交叉表/透视查询来完成此操作，但这不是联接应该做的事情吗？

编辑：更新以显示我的加入查询。 Datetable 只有 8 行，每个日历周有 1 个增量数字。

SELECT * FROM @datetable
    LEFT JOIN
    (SELECT 
        sls_vendor,
        sls_item,
        sls_units,
        datepart(week,sls_week) AS sls_date
    FROM mytable) AS QRY
    ON temp_week = qry.sls_date

Answer 1

您的方法应该可以正常工作：

;with mytable as (
    select 1 as sls_vendor, 'Test' as sls_item, 30 as sls_units, '8/7/2011' as sls_week union
    select 1 as sls_vendor, 'Test' as sls_item, 30 as sls_units, '8/14/2011' as sls_week union
    select 1 as sls_vendor, 'Test' as sls_item, 30 as sls_units, '8/21/2011' as sls_week
)
,datetable as (
    select 28 as temp_week union
    select 29 union
    select 30 union
    select 31 union
    select 32 union
    select 33 union
    select 34 union
    select 35
)
SELECT * FROM datetable

    LEFT JOIN

    (SELECT 
        sls_vendor,
        sls_item,
        sls_units,
        datepart(week,sls_week) AS sls_date
    FROM mytable) AS QRY

    ON temp_week=qry.sls_date

输出：

temp_week   sls_vendor  sls_item    sls_units   sls_date
28          NULL        NULL        NULL        NULL
29          NULL        NULL        NULL        NULL
30          NULL        NULL        NULL        NULL
31          NULL        NULL        NULL        NULL
32          NULL        NULL        NULL        NULL
33          1           Test        30          33
34          1           Test        30          34
35          1           Test        30          35

编辑：如果您想包含每个销售供应商的所有周值，请包含与不同供应商选择的交叉连接：

;with mytable as (
    select 1 as sls_vendor, 'Test' as sls_item, 30 as sls_units, '8/7/2011' as sls_week union
    select 2 as sls_vendor, 'Test' as sls_item, 30 as sls_units, '8/14/2011' as sls_week union
    select 3 as sls_vendor, 'Test' as sls_item, 30 as sls_units, '8/21/2011' as sls_week
)
,datetable as (
    select 28 as temp_week union
    select 29 union
    select 30 union
    select 31 union
    select 32 union
    select 33 union
    select 34 union
    select 35
)
SELECT * FROM datetable
cross join (select distinct sls_vendor from mytable) v

    LEFT JOIN

    (SELECT 
        sls_vendor,
        sls_item,
        sls_units,
        datepart(week,sls_week) AS sls_date
    FROM mytable) AS QRY

    ON temp_week=qry.sls_date and v.sls_vendor=qry.sls_vendor

输出：

temp_week   sls_vendor  sls_vendor  sls_item    sls_units   sls_date
28          1           NULL        NULL        NULL        NULL
29          1           NULL        NULL        NULL        NULL
30          1           NULL        NULL        NULL        NULL
31          1           NULL        NULL        NULL        NULL
32          1           NULL        NULL        NULL        NULL
33          1           1           Test        30          33
34          1           NULL        NULL        NULL        NULL
35          1           NULL        NULL        NULL        NULL
28          2           NULL        NULL        NULL        NULL
29          2           NULL        NULL        NULL        NULL
30          2           NULL        NULL        NULL        NULL
31          2           NULL        NULL        NULL        NULL
32          2           NULL        NULL        NULL        NULL
33          2           NULL        NULL        NULL        NULL
34          2           2           Test        30          34
35          2           NULL        NULL        NULL        NULL
28          3           NULL        NULL        NULL        NULL
29          3           NULL        NULL        NULL        NULL
30          3           NULL        NULL        NULL        NULL
31          3           NULL        NULL        NULL        NULL
32          3           NULL        NULL        NULL        NULL
33          3           NULL        NULL        NULL        NULL
34          3           NULL        NULL        NULL        NULL
35          3           3           Test        30          35

Answer 2

它对你有用吗？

SELECT sls_vendor, 
   sls_item, 
   sls_units, 
   DATEPART(WEEK, sls_week) AS sls_date 
FROM (
    SELECT VALUE = 28 UNION ALL 
    SELECT VALUE = 29 UNION ALL 
    SELECT VALUE = 30 UNION ALL  
    SELECT VALUE = 31 UNION ALL  
    SELECT VALUE = 32 UNION ALL  
    SELECT VALUE = 33 UNION ALL  
    SELECT VALUE = 34 UNION ALL 
    SELECT VALUE = 35
) dates
LEFT JOIN mytable m
ON dates.value = DATEPART(WEEK, m.sls_week)

Answer 3

以下查询适用于 Data.StackExchange。 请参阅此处。按分数计算，它每周都会排名第一。

WITH weeksyears 
     AS (SELECT w.NUMBER AS week, 
                y.NUMBER AS year
         FROM   (SELECT v.NUMBER 
                 FROM   MASTER..spt_values v 
                 WHERE  TYPE = 'P' 
                        AND v.NUMBER BETWEEN 1 AND 52) w, 
                (SELECT v.NUMBER 
                 FROM   MASTER..spt_values v 
                 WHERE  TYPE = 'P' 
                        AND v.NUMBER BETWEEN 2008 AND 2012) y), 
     topPostPerWeek
     AS (SELECT score, 
                Datepart(week, creationdate)       week, 
                Datepart(YEAR, creationdate)       YEAR, 
                Row_number() OVER (PARTITION BY Datepart(wk, creationdate), 
                Datepart( 
                   YEAR, 
                creationdate) ORDER BY score DESC) row 
         FROM   posts) 
SELECT * 
FROM   weeksyears wy 
       LEFT JOIN topPostPerWeekt 
         ON wy.week = t.week 
            AND wy.YEAR = t.YEAR 
WHERE  row = 1 
OR row IS NULL 
ORDER BY wy.YEAR, wy.WEEK
​

2008 年第 38 周之前的每一行都是空的，除了周和年。以及 2011 年第 35 周之后的行。

但是，如果您编辑查询并删除 OR row IS NULL，则查询的行为就像 INNER JOIN

我的猜测是，您的 WHERE 中有一些内容引用了“RIGHT”表。只需添加 OR [rightTable.field] IS NULL 就可以了。