Sql Server：按月、年和按某项分组计算数据

Question

首先，我的表结构类似于这样：

CREATE TABLE Testing(
       [ID]        NVARCHAR(50),
       [DATE]      DATETIME,
       [TOTAL]     INT,
       [ITEM]      NVARCHAR(50),
       [Warehouse] NVARCHAR(50)
)ON[PRIMARY]

我在这里放了一些示例：

[ID] [Date]      [Total] [Item] [Warehouse]
1    2011-04-04  400     A0001   B12
2    2011-05-04  500     A0001   B13
3    2011-04-30  400     A0001   B12
4    2011-04-25  400     A0001   B13
5    2011-06-05  600     A0001   B12
6    2011-03-02  300     A0001   B11
7    2011-05-28  500     A0001   B13   

我也尝试按 [Item] 和 [Warehouse] 和 [Date] 按月进行分组 例如输出：

结果应该是这样的

[Date]        [Total]   [Item]  [Warehouse]
March 2011     300       A0001   B11
April 2011     800       A0001   B12
June 2011      500       A0001   B12
April 2011     400       A0001   B13
May 2011       1000      A0001   B13

我尝试了类似的sql，我在月份和年份部分解析以做出选择

SELECT [Item],[Warehouse],SUM(Total) AS Total
FROM [Testing]
WHERE Datepart(month,[Date]) = 4 AND DATEPART(year,[Date]) = 2011
GROUP BY [Item],[Warehouse]

我得到了预期的结果？有什么办法吗？我实际上试图根据仓库和项目来得出每个月和每年的接近平衡？

对我来说，它需要循环遍历前缀表。 无论如何都要这样做吗？

谢谢

问候 良克

Answer 1

查看以下函数

DATENAME (Transact-SQL) 和 < a href="http://msdn.microsoft.com/en-us/library/aa258265%28v=sql.80%29.aspx" rel="nofollow">DATEPART

看一下下面的示例

DECLARE @Table TABLE(
        DateValue DATETIME
)

INSERT INTO @Table SELECT '01 Jan 2011'
INSERT INTO @Table SELECT '01 Feb 2011'
INSERT INTO @Table SELECT '01 Feb 2011'

SELECT  DATENAME(month,DateValue) + ' ' + CAST(DATEPART(year,DateValue) AS VARCHAR(4)),
        COUNT(1) TotalCnt
FROM    @Table
GROUP BY DATENAME(month,DateValue) + ' ' + CAST(DATEPART(year,DateValue) AS VARCHAR(4))

结果：

February 2011   2
January 2011    1

Answer 2

我认为在 SQL Server 端格式化日期（通常）是一个想法，但无论如何你可以尝试：

select datename(mm, dateadd(m, datediff(m, 0, [date]), 0)) + ' ' +
      select cast(datepart(yy, dateadd(m, datediff(m, 0, [date]), 0)) as varchar)
      [date] sum(total) as total, item,warehouse
from testing
group by item, warehouse, dateadd(m, datediff(m, 0, [date]), 0)