如何在 MySQL 中按年和月分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14019964/
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 to Group By Year and Month in MySQL
提问by Ben
I would like to measure the count of ID and ATTRIBUTE from the source table and present the data as shown in the "Desired Report" below. I am using MySQL.
我想从源表中测量 ID 和 ATTRIBUTE 的计数，并显示如下“所需报告”中所示的数据。我正在使用 MySQL。
Source:
来源：
ID | DATE | ATTRIBUTE
--------------------------------
1 | 2012-01-14 | XYZ
2 | 2012-03-14 |
3 | 2012-03-15 | XYZ
4 | 2012-04-24 | ABC
5 | 2012-04-10 |
6 | 2012-05-11 | ABC
Desired Reports:
期望的报告：
Count of Attribute
属性计数
YEAR | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC
---------------------------------------------------------------------------
2010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
2011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
2012 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
Count of ID
身件数
YEAR | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC
---------------------------------------------------------------------------
2010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
2011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
2012 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
Percentage Complete ( Count of Attribute / Count of ID )
完成百分比（属性计数/ ID 计数）
YEAR | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC
---------------------------------------------------------------------------
2010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
2011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
2012 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
Here's the code I have so far. Thanks! And Also keep in mind, I need to extract the Month from the date field in my data but not sure how. Thanks.
这是我到目前为止的代码。谢谢！另外请记住，我需要从数据中的日期字段中提取月份，但不确定如何提取。谢谢。
SELECT YEAR(document_filing_date),MONTH(document_filing_date),COUNT(aif_id)
FROM (a_aif_remaining)
GROUP BY YEAR(document_filing_date),MONTH(document_filing_date);
Suggested answer doesn't work!! Not sure why, here is the error I get:
建议的答案不起作用！！不知道为什么，这是我得到的错误：
"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' COUNT(CASE WHEN MONTH(document_filing_date) = 1 THEN aif_id END) AS Jan, CO' at line 1"
SELECT YEAR(document_filing_date,
COUNT(CASE WHEN MONTH(document_filing_date) = 1 THEN aif_id END) AS Jan,
COUNT(CASE WHEN MONTH(document_filing_date) = 2 THEN aif_id END) AS Feb,
COUNT(CASE WHEN MONTH(document_filing_date) = 3 THEN aif_id END) AS Mar,
COUNT(CASE WHEN MONTH(document_filing_date) = 4 THEN aif_id END) AS Apr,
COUNT(CASE WHEN MONTH(document_filing_date) = 5 THEN aif_id END) AS May,
COUNT(CASE WHEN MONTH(document_filing_date) = 6 THEN aif_id END) AS Jun,
COUNT(CASE WHEN MONTH(document_filing_date) = 7 THEN aif_id END) AS Jul,
COUNT(CASE WHEN MONTH(document_filing_date) = 8 THEN aif_id END) AS Aug,
COUNT(CASE WHEN MONTH(document_filing_date) = 9 THEN aif_id END) AS Sep,
COUNT(CASE WHEN MONTH(document_filing_date) = 10 THEN aif_id END) AS Oct,
COUNT(CASE WHEN MONTH(document_filing_date) = 11 THEN aif_id END) AS Nov,
COUNT(CASE WHEN MONTH(document_filing_date) = 12 THEN aif_id END) AS Dec,
FROM a_aif_remaining
GROUP BY YEAR(document_filing_date);
回答by fthiella
This query will count all the rows, and will also count just the rows where Attributeis not null, grouping by year and month in rows:
此查询将计算所有行，并且还将仅计算Attribute不为空的行，按行中的年和月分组：
SELECT
Year(`date`),
Month(`date`),
Count(*) As Total_Rows,
Count(`Attribute`) As Rows_With_Attribute
FROM your_table
GROUP BY Year(`date`), Month(`date`)
(this because Count(*) counts all the rows, Count(Attibute) counts all the rows where Attribute is not null)
（这是因为 Count(*) 计算所有行，Count(Attibute) 计算 Attribute 不为 null 的所有行）
If you need your table in PIVOT, you can use this to count only the rows where Attribute is not null:
如果您需要 PIVOT 中的表，您可以使用它来仅计算 Attribute 不为空的行：
SELECT
Year(`date`),
Count(case when month(`date`)=1 then `Attribute` end) As Jan,
Count(case when month(`date`)=2 then `Attribute` end) As Feb,
Count(case when month(`date`)=3 then `Attribute` end) As Mar,
...
FROM your_table
GROUP BY Year(`date`)
And this to count all the rows:
这可以计算所有行：
SELECT
Year(`date`),
Count(case when month(`date`)=1 then id end) As Jan,
Count(case when month(`date`)=2 then id end) As Feb,
Count(case when month(`date`)=3 then id end) As Mar,
...
FROM your_table
GROUP BY Year(`date`)
(or, instead of counting id, you can use Sum(Month(date)=1)like in kander's answer). Of course you can combine both queries into this:
（或者，您可以像 kander 的答案一样使用Sum(Month(日期，而不是计算 id )=1)）。当然，您可以将两个查询组合成这样：
SELECT
Year(`date`),
Count(case when month(`date`)=1 then id end) As Jan_Tot,
Count(case when month(`date`)=1 then `Attribute` end) As Jan_Attr,
Count(case when month(`date`)=2 then id end) As Feb_Tot,
Count(case when month(`date`)=2 then `Attribute` end) As Feb_Attr,
Count(case when month(`date`)=3 then id end) As Mar_Tot,
Count(case when month(`date`)=3 then `Attribute` end) As Mar_Attr,
...
FROM your_table
GROUP BY Year(`date`)
回答by kander
I've come up with the following, but have a feeling that there might be a more efficient way to go about this...
我想出了以下内容，但感觉可能有更有效的方法来解决这个问题......
select
year(`date`) as 'YEAR',
sum(month(`date`) = 1) as 'JAN',
sum(month(`date`) = 2) as 'FEB',
sum(month(`date`) = 3) as 'MAR',
-- Repeat until december
FROM `source`
WHERE `ATTRIBUTE` = 'XYZ'
GROUP BY 1;
