SQL Server 按月分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8345282/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server Group By Month
提问by Echilon
I have a table which has this schema
我有一个具有此架构的表
ItemID UserID Year IsPaid PaymentDate Amount
1 1 2009 0 2009-11-01 300
2 1 2009 0 2009-12-01 342
3 1 2010 0 2010-01-01 243
4 1 2010 0 2010-02-01 2543
5 1 2010 0 2010-03-01 475
I'm trying to get a query working which shows the totals for each month. So far I've tried DateDiff and nested selects, but neither gives me what I want. This is the closest I have I think:
我试图让一个查询工作，它显示每个月的总数。到目前为止，我已经尝试过 DateDiff 和嵌套选择，但都没有给我我想要的。这是我认为最接近的：
DECLARE @start [datetime] = 2010/4/1;
SELECT ItemID, IsPaid,
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And DateDiff(m, PaymentDate, @start) = 0 AND UserID = 100) AS "Apr",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =1 AND UserID = 100) AS "May",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =2 AND UserID = 100) AS "Jun",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =3 AND UserID = 100) AS "Jul",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =4 AND UserID = 100) AS "Aug",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =5 AND UserID = 100) AS "Sep",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =6 AND UserID = 100) AS "Oct",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =7 AND UserID = 100) AS "Nov",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =8 AND UserID = 100) AS "Dec",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =9 AND UserID = 100) AS "Jan",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =10 AND UserID = 100) AS "Feb",
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 AND DateDiff(m, PaymentDate, @start) =11 AND UserID = 100) AS "Mar"
FROM LIVE L INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY
WHERE UserID = 16178
But I just get nulls when I should be getting values. Am I missing something?
但是当我应该获取值时，我只会得到空值。我错过了什么吗？
回答by Dave Downs
SELECT CONVERT(NVARCHAR(10), PaymentDate, 120) [Month], SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY CONVERT(NVARCHAR(10), PaymentDate, 120)
ORDER BY [Month]
You could also try:
你也可以试试：
SELECT DATEPART(Year, PaymentDate) Year, DATEPART(Month, PaymentDate) Month, SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY DATEPART(Year, PaymentDate), DATEPART(Month, PaymentDate)
ORDER BY Year, Month
回答by Martyn Davis
Restrict the dimension of the NVARCHAR to 7, supplied to CONVERT to show only "YYYY-MM"
将 NVARCHAR 的维度限制为 7，提供给 CONVERT 以仅显示“YYYY-MM”
SELECT CONVERT(NVARCHAR(7),PaymentDate,120) [Month], SUM(Amount) [TotalAmount]
FROM Payments
GROUP BY CONVERT(NVARCHAR(7),PaymentDate,120)
ORDER BY [Month]
回答by bounav
I prefer combining DATEADDand DATEDIFFfunctions like this:
我更喜欢这样的组合DATEADD和DATEDIFF功能：
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Created),0)
Together, these two functions zero-out the date component smallerthan the specified datepart(i.e. MONTHin this example).
一起，这两个功能零时的日期分量较小比指定的日期部分（即MONTH在此实例中）。
You can change the datepartbit to YEAR, WEEK, DAY, etc... which is super handy.
您可以更改日期部分位YEAR，WEEK，DAY，等...这是超级方便。
Your original SQL query would then look something like this (I can't test it as I don't have your data set, but it should put you on the right track).
您的原始 SQL 查询将看起来像这样（我无法测试它，因为我没有您的数据集，但它应该让您走上正轨）。
DECLARE @start [datetime] = '2010-04-01';
SELECT
ItemID,
UserID,
DATEADD(MONTH, DATEDIFF(MONTH, 0, Created),0) [Month],
IsPaid,
SUM(Amount)
FROM LIVE L
INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY
WHERE UserID = 16178
AND PaymentDate > @start
One more thing: the Monthcolumn is typed as a DateTimewhich is also a nice advantage if you need to further process that data or map it .NET object for example.
还有一件事：Month列被输入为 aDateTime这也是一个很好的优势，例如，如果您需要进一步处理该数据或将其映射到 .NET 对象。
回答by MatBailie
DECLARE @start [datetime] = 2010/4/1;
Should be...
应该...
DECLARE @start [datetime] = '2010-04-01';
The one you have is dividing 2010 by 4, then by 1, then converting to a date. Which is the 57.5th day from 1900-01-01.
您拥有的是将 2010 除以 4，然后除以 1，然后转换为日期。这是从 1900-01-01 开始的第 57.5 天。
Try SELECT @startafter your initialisation to check if this is correct.
SELECT @start初始化后尝试检查这是否正确。
回答by marc_s
If you need to do this frequently, I would probably add a computed column PaymentMonthto the table:
如果您需要经常这样做，我可能会PaymentMonth在表中添加一个计算列：
ALTER TABLE dbo.Payments ADD PaymentMonth AS MONTH(PaymentDate) PERSISTED
It's persisted and stored in the table - so there's really no performance overhead querying it. It's a 4 byte INT value - so the space overhead is minimal, too.
它被持久化并存储在表中 - 所以查询它真的没有性能开销。它是一个 4 字节的 INT 值 - 因此空间开销也很小。
Once you have that, you could simplify your query to be something along the lines of:
一旦有了它，您就可以将查询简化为以下内容：
SELECT ItemID, IsPaid,
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And PaymentMonth = 1 AND UserID = 100) AS 'Jan',
(SELECT SUM(Amount) FROM Payments WHERE Year = 2010 And PaymentMonth = 2 AND UserID = 100) AS 'Feb',
.... and so on .....
FROM LIVE L
INNER JOIN Payments I ON I.LiveID = L.RECORD_KEY
WHERE UserID = 16178
回答by Dai
Another approach, that doesn't involve adding columns to the result, is to simply zero-out the daycomponent of the date, so 2016-07-13and 2016-07-16would both be 2016-07-01- thus making them equal by month.
另一种方法，不涉及添加列的结果，是简单地为零时的day日期的组成部分，因此2016-07-13并2016-07-16都希望能2016-07-01-从而使他们每月相等。
If you have a date(not a datetime) value, then you can zero it directly:
如果您有一个date（不是 a datetime）值，那么您可以直接将其归零：
SELECT
DATEADD( day, 1 - DATEPART( day, [Date] ), [Date] ),
COUNT(*)
FROM
[Table]
GROUP BY
DATEADD( day, 1 - DATEPART( day, [Date] ), [Date] )
If you have datetimevalues, you'll need to use CONVERTto remove the time-of-day portion:
如果您有datetime值，则需要使用CONVERT来删除时间部分：
SELECT
DATEADD( day, 1 - DATEPART( day, [Date] ), CONVERT( date, [Date] ) ),
COUNT(*)
FROM
[Table]
GROUP BY
DATEADD( day, 1 - DATEPART( day, [Date] ), CONVERT( date, [Date] ) )
回答by DRapp
Now your query is explicitly looking at only payments for year = 2010, however, I think you meant to have your Jan/Feb/Mar actually represent 2009. If so, you'll need to adjust this a bit for that case. Don't keep requerying the sum values for every column, just the condition of the date difference in months. Put the rest in the WHERE clause.
现在您的查询明确地只查看 2010 年的付款，但是，我认为您的意思是让您的 Jan/Feb/Mar 实际上代表 2009 年。如果是这样，您需要针对这种情况稍微调整一下。不要不断地重新查询每一列的总和值，只是以月为单位的日期差异的条件。将其余部分放在 WHERE 子句中。
SELECT
SUM( case when DateDiff(m, PaymentDate, @start) = 0
then Amount else 0 end ) AS "Apr",
SUM( case when DateDiff(m, PaymentDate, @start) = 1
then Amount else 0 end ) AS "May",
SUM( case when DateDiff(m, PaymentDate, @start) = 2
then Amount else 0 end ) AS "June",
SUM( case when DateDiff(m, PaymentDate, @start) = 3
then Amount else 0 end ) AS "July",
SUM( case when DateDiff(m, PaymentDate, @start) = 4
then Amount else 0 end ) AS "Aug",
SUM( case when DateDiff(m, PaymentDate, @start) = 5
then Amount else 0 end ) AS "Sep",
SUM( case when DateDiff(m, PaymentDate, @start) = 6
then Amount else 0 end ) AS "Oct",
SUM( case when DateDiff(m, PaymentDate, @start) = 7
then Amount else 0 end ) AS "Nov",
SUM( case when DateDiff(m, PaymentDate, @start) = 8
then Amount else 0 end ) AS "Dec",
SUM( case when DateDiff(m, PaymentDate, @start) = 9
then Amount else 0 end ) AS "Jan",
SUM( case when DateDiff(m, PaymentDate, @start) = 10
then Amount else 0 end ) AS "Feb",
SUM( case when DateDiff(m, PaymentDate, @start) = 11
then Amount else 0 end ) AS "Mar"
FROM
Payments I
JOIN Live L
on I.LiveID = L.Record_Key
WHERE
Year = 2010
AND UserID = 100
