如何在 MySQL 中对日期字段进行分组以获得季度结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6067526/
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
How do I group a date field to get quarterly results in MySQL?
提问by abnab
I have a job table that holds jobs and leaddate is the field for the job entry.
我有一个保存工作的工作表，而 Leaddate 是工作条目的字段。
The result I want to get is the number of jobs I have in each quarter. My query counts jobs of each date in the leaddate field.
我想得到的结果是我每个季度的工作数量。我的查询计算了 Leaddate 字段中每个日期的工作。
Here is the query
这是查询
select count(jobid) as jobcount, leaddate
from jobs
where contactid='19249'
group by leaddate
回答by Jonathan Leffler
I think this should do the job:
我认为这应该可以完成这项工作：
SELECT YEAR(leaddate) AS year, QUARTER(leaddate) AS quarter, COUNT(jobid) AS jobcount
FROM jobs
WHERE contactid = '19249'
GROUP BY YEAR(leaddate), QUARTER(leaddate)
ORDER BY YEAR(leaddate), QUARTER(leaddate)
回答by zerkms
Supposing you have a valid date or datetime field:
假设您有一个有效的日期或日期时间字段：
select count(jobid) as jobcount, YEAR(leaddate) yr, QUARTER(leaddate) qt
from jobs
where contactid='19249'
group by yr, qt
回答by Headshota
you can use the Quarter function to get a quarter from date:
您可以使用 Quarter 函数从日期中获取一个季度：
select count(jobid) as jobcount, QUARTER(leaddate) as qt, YEAR(leaddate) as year
from jobs
where contactid='19249'
group by year,qt
回答by Marc Maxmeister
I usually combine quarter and year into a single YEAR-QUARTER field:
我通常将季度和年度合并为一个 YEAR-QUARTER 字段：
select jobid, count(jobid) as jobcount,
CONCAT( YEAR(leaddate),'-',QUARTER(leaddate) ) as year_qtr
from jobs
where contactid='19249'
group by year_qtr
RETURNS data like:
返回数据，如：
19249, 324, 2011-3
19249, 4, 2011-2
19249, 34, 2011-1
