SQL 按月分组日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5107562/
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 group dates by month
提问by user380432
I have a query that returns expiration dates:
我有一个返回到期日期的查询：
SELECT ci.accountnumber
, ci.accountname
, cvu.ExpirationDate
FROM dbo.clientinfo ci
INNER JOIN clientvehicleunit cvu ON ci.clientid = cvu.clientid
The expiration dates can be anytime during any month and any year.
到期日期可以是任何月份和任何年份的任何时间。
I need to return counts of how many units are due to expire within each month for a 12 month period....
我需要返回 12 个月内每个月内到期的单位数......
I have no idea how I would do this?
我不知道我将如何做到这一点？
回答by AdaTheDev
You can do something like this:
你可以这样做：
e.g. how many units are due to expire in 2012:
例如，有多少单位将在 2012 年到期：
SELECT MONTH(cvu.ExpirationDate) AS Mnth, YEAR(cvu.ExpirationDate) AS Yr,
COUNT(*) AS DueToExpire
FROM clientvehicleunit cvu
WHERE cvu.ExpirationDate >= '20120101' AND cvu.ExpirationDate < '20130101'
GROUP BY MONTH(cvu.ExpirationDate), YEAR(cvu.ExpirationDate)
回答by RichardTheKiwi
I need to return counts of how many units are due to expire within each month for a 12 month period.
我需要返回 12 个月内每个月到期的单位数量。
If you mean from the current month forward, then
如果你的意思是从当月开始，那么
SELECT
[YYYY.MM] = CONVERT(varchar(7), cvu.ExpirationDate, 102),
CountInMonth = COUNT(*)
FROM dbo.clientinfo ci
JOIN clientvehicleunit cvu ON ci.clientid = cvu.clientid
WHERE cvu.ExpirationDate >= DATEADD(m, DATEDIFF(m,0,getdate()), 0)
AND cvu.ExpirationDate < DATEADD(m, DATEDIFF(m,0,getdate())+12, 0)
GROUP BY CONVERT(varchar(7), cvu.ExpirationDate, 102)
ORDER BY [YYYY.MM]
Note: The period is printed in the form [YYYY.MM], e.g. 2011.01
注：句号以[YYYY.MM]的形式打印，例如2011.01
