通过日期范围选择结果，包括不存在结果的null - Select results through date range, including null where results do not exist

这是Microsoft SQL，不是Mysql。

我有一个保存发送给客户的货物的发票数据的表。

例如，数据可能如下所示:

INVOICE DATE | QUANTITY | PRODUCT | VENDOR ID
2014-07-31   | 25       | Oranges | 12
2014-08-12   | 14       | Apples  | 12
2014-09-01   | 135      | Oranges | 12
2015-07-04   | 18       | Oranges | 12
2015-07-12   | 35       | Apples  | 12

如您所见，数据中存在空白，我们没有在特定月份向该客户发送订单。

我试图得到一个月的列表，所以我可以在图表上显示这个(chartJS具体来说)，所以我需要空值，这些记录不存在，所以我们有一个字符串的12个值，即使它们都是零给定的一年。

现在，我们在PHP循环中使用异构查询(非常非常糟糕)来实现这一点，就像这样

while($i = 1; $i < 13; $i++) {
    $query = "Select sum(quantity), month, year
        From Table
        where month = $i
        and year = $year
        and vendorID = 12"
}

结果是如下所示的数据集，我们得到每个月，如果没有结果，我们得到0。

month | year | quantity
...   | ...  | ...
6     | 2014 | 0
7     | 2014 | 25
8     | 2014 | 14
9     | 2014 | 135
10    | 2014 | 0
....  | .... | ....

不幸的是，我们想要显示的每一年需要12个查询。我希望能够在一个查询中获得相同的数据。

不幸的是，更新后的查询只在发票日期存在时返回结果。是否有一种方法可以使用SQL而不是PHP循环遍历所有可能的月份，以便使用聚合查询获得此信息?

我已经得到了以下查询工作，但它只给我一个发票日期存在的结果:

Select sum(quantity), month, year
from table where VendorID = 12
and InvoiceDate between '1/1/2014' and '12/31/2014'
GROUP BY year, month

结果:

Quantity | Month | Year
25       | 7     | 2014
14       | 8     | 2014
135      | 9     | 2014
53       | 7     | 2015

正如您所看到的，这只提供带有值的结果，而不是所有的月份都是零。

<标题> 解决方案

感谢贡献者，我有一个解决方案查询工作，某种程度上结合了两种建议

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
Set @StartDate = '7/1/2014'
SET @EndDate = '12/1/2015'
;with cal as (
    SELECT @StartDate AS [Date]
    UNION ALL 
    SELECT DATEADD(month, 1, [Date])
    FROM cal
    WHERE [Date] < @EndDate
)
select coalesce(a.qty, 0) as qty, 
DATEPART(month, cal.[Date]) AS [month], 
DATEPART(year, cal.[Date]) AS [year] from
(
    SELECT sum(qty) as qty, 
    [month], 
    [year]
    from ship_table
    WHERE vendorID = 12
    group by [year], [month]
) A
right join cal ON DATEPART(month, cal.[Date])=A.[month]
AND DATEPART(year, cal.[Date])=A.[year]

请尝试以下查询。我用pivot来填充月份的数字。我假设年份保持不变，即2014(这证明使用ISNULL(year,2014)是合理的)，或者可以以某种方式从参数(如YEAR(@startmonth)

)中导出。

我还创建了一个sql提琴演示:http://sqlfiddle.com/!6/9dc17/2

select ISNULL(A.quantity,0) as quantity,cal.Num as Month,ISNULL(year,2014) from
(Select sum(quantity) as quantity, month(InvoiceDate) as month, year(InvoiceDate) as year
from [table]
where VendorID = 12
and InvoiceDate between '1/1/2014' and '12/31/2014'
GROUP BY year(InvoiceDate), month(InvoiceDate)) A
right join
(select mon,num from
    (
    select 1 as [Jan],2 as [feb],3 as [mar],4 as  [apr],5 as [may],6 as [jun],7 as [jul],8 as [aug],9 as [sep],10 as [oct],11 as [nov],12 as [dec]) s
    UNPIVOT
    (
    Num for Mon in ([jan],[feb],[mar],[apr],[may],[jun],[jul],[aug],[sep],[oct],[nov],[dec])
    )up) cal
    on A.month=Cal.Num

这就是我想要的答案。

根据你的问题中的查询代码，我假设你的表有名为"月"answers"年"的列，它们可以像在你的查询中一样使用。

假设您将传递StartDate和EndDate参数到您的查询，因此您可以获得任何日期范围:

WITH cte AS (
  SELECT @StartDate AS [Date]
  UNION ALL 
  SELECT DATEADD(month, 1, [Date])
  FROM cte
  WHERE [Date] <= @EndDate
)
Select 
  sum(quantity), 
  DATEPART(month, cte.[Date]) AS [month], 
  DATEPART(year, cte.[Date]) AS [year]
from table t
RIGHT OUTER JOIN cte
  ON DATEPART(month, cte.[Date])=t.month
  AND DATEPART(year, cte.[Date])=t.year
where VendorID = 12
GROUP BY   DATEPART(month, cte.[Date]) AS [month], 
  DATEPART(year, cte.[Date]) AS [year]