sql - 如何在 SQL Server 中每月 31 天按天循环执行 PIVOT

Question

我有这张表:

CREATE TABLE [dbo].[tblProducts]
(
    [CATEGORY_ID] [nvarchar](50) NULL,
    [PRODUCT] [nvarchar](50) NULL,
    [PRODUCTION_DATE] [datetime] NULL
) ON [PRIMARY]

我需要显示一个月中每一天的总产品

应该调整天数

CATE   1 2 3 4 5 6 7.....30 (or 31)  -->days of month
CATE1  1 5                           --> count product by cate and day
CATE2  1 9
CATE3  5 10

请注意生产日期是从当前日期的早上 6 点开始

到下一个日期的凌晨 5.59

这就是我的难处。

请帮帮我。

Answer 1

这是基本的数据透视查询:

Declare @month int = 1
; With data as (
    Select [CATEGORY_ID], [PRODUCT], [PRODUCTION_DATE] = DATEPART(DAY, DATEADD(HOUR, -6, [PRODUCTION_DATE]))
    , [PRODUCT_YEAR] = DATEPART(YEAR, [PRODUCTION_DATE]), [PRODUCTION_MONTH] = DATEPART(MONTH, [PRODUCTION_DATE]) From [dbo].[tblProducts]
    --Where DATEPART(MONTH, DATEADD(HOUR, -6,[PRODUCTION_DATE])) = @month update if monthly/yearly query is needed
)
Select [CATEGORY_ID], [PRODUCTION_MONTH], [PRODUCTION_YEAR], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
From data
Pivot (
    Count([PRODUCT])
    For [PRODUCTION_DATE] In
    ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
    ) as piv
    Order By [CATEGORY_ID], [PRODUCTION_YEAR], [PRODUCTION_MONTH]

我基本上将所有内容都偏移了 6 小时 (-6)。 20150101 06:00 变为前一天的午夜 20150101 00:00 和 5:59 变为 23;59。

枢轴:https://technet.microsoft.com/fr-fr/library/ms177410%28v=SQL.105%29.aspx