加入收藏 | 设为首页 | 会员中心 | 我要投稿 南京站长网 (https://www.025zz.com.cn/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 站长学院 > MsSql教程 > 正文

SQL Group和Sum By Month – 默认为零

发布时间:2021-01-20 01:19:02 所属栏目:MsSql教程 来源:网络整理
导读:我目前正在按月分组和汇总库存使用情况: SELECT Inventory.itemid AS ItemID,SUM(Inventory.Totalunits) AS Individual_MonthQty,MONTH(Inventory.dadded) AS Individual_MonthAsNumber,DATENAME(MONTH,Inventory.dadded) AS Individual_MonthAsStringFRO

我目前正在按月分组和汇总库存使用情况:

SELECT      Inventory.itemid AS ItemID,SUM(Inventory.Totalunits) AS Individual_MonthQty,MONTH(Inventory.dadded) AS Individual_MonthAsNumber,DATENAME(MONTH,Inventory.dadded) AS Individual_MonthAsString
FROM        Inventory
WHERE       Inventory.invtype = 'Shipment'
AND         Inventory.dadded >= @StartRange
AND         Inventory.dadded <= @EndRange
GROUP BY    Inventory.ItemID,MONTH(Inventory.dadded),Inventory.dadded)

这给了我期待的结果:

ItemID  Kit_MonthQty    Kit_MonthAsNumber   Kit_MonthAsString
13188   234             8                   August
13188   45              9                   September
13188   61              10                  October
13188   20              12                  December

在没有数据存在的月份,我必须做什么才能返回零,如下所示:

ItemID  Kit_MonthQty    Kit_MonthAsNumber   Kit_MonthAsString
13188   0               1                   January
13188   0               2                   February
13188   0               3                   March
13188   0               4                   April
13188   0               5                   May
13188   0               6                   June
13188   0               7                   July
13188   234             8                   August
13188   45              9                   September
13188   61              10                  October
13188   0               11                  November
13188   20              12                  December

解决方法

在过去,我通过创建一个临时表来解决这样的问题,该表将保存所需的所有日期:
CREATE TABLE #AllDates (ThisDate datetime null)
    SET @CurrentDate = @StartRange

    -- insert all dates into temp table
    WHILE @CurrentDate <=  @EndRange
        BEGIN
            INSERT INTO #AllDates values(@CurrentDate)
            SET @CurrentDate = dateadd(mm,1,@CurrentDate)
        END

然后,修改您的查询以加入此表:

SELECT      ALLItems.ItemId,SUM(COALESCE(Inventory.Qty,0)) AS Individual_MonthQty,MONTH(#AllDates.ThisDate) AS Individual_MonthAsNumber,#AllDates.ThisDate) AS Individual_MonthAsString
FROM        #AllDates
            JOIN (SELECT DISTINCT dbo.Inventory.ItemId FROM dbo.Inventory)  AS ALLItems ON 1 = 1
            LEFT JOIN Inventory ON DATEADD(dd,- DAY(Inventory.dadded) +1,Inventory.dadded) = #AllDates.ThisDate AND ALLItems.ItemId = dbo.Inventory.ItemId
WHERE       
            #AllDates.ThisDate >= @StartRange
AND         #AllDates.ThisDate <= @EndRange
GROUP BY    ALLItems.ItemId,#AllDates.ThisDate

那么你应该有一个每个月的记录,无论它是否存在于库存中.

(编辑:南京站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读