使用SQL如何通过组一个月日期字段(How to group by month from Date

我怎么能组只按月从日期字段（而不是由天团）？

这里是我的日期字段的样子：

2012-05-01

这是我当前的SQL：

select  Closing_Date, Category,  COUNT(Status)TotalCount from  MyTable
where Closing_Date >= '2012-02-01' and Closing_Date <= '2012-12-31'
and Defect_Status1 is not null
group by  Closing_Date, Category

Answer 1:

我这样做：

SELECT  Closing_Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), 
        Category,  
        COUNT(Status) TotalCount 
FROM    MyTable
WHERE   Closing_Date >= '2012-02-01' 
AND     Closing_Date <= '2012-12-31'
AND     Defect_Status1 IS NOT NULL
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), Category;

在每个月的第一天这将组，所以

`DATEADD(MONTH, DATEDIFF(MONTH, 0, '20130128'), 0)` 

会给'20130101' 。 因为它保持日期为日期我一般喜欢这种方法。

另外，您可以使用这样的：

SELECT  Closing_Year = DATEPART(YEAR, Closing_Date),
        Closing_Month = DATEPART(MONTH, Closing_Date),
        Category,  
        COUNT(Status) TotalCount 
FROM    MyTable
WHERE   Closing_Date >= '2012-02-01' 
AND     Closing_Date <= '2012-12-31'
AND     Defect_Status1 IS NOT NULL
GROUP BY DATEPART(YEAR, Closing_Date), DATEPART(MONTH, Closing_Date), Category;

这真的取决于你的期望的输出是什么。 （截止年份是没有必要在你的榜样，但如果日期范围跨越了一年的边界可能）。

Answer 2:

使用DATEPART函数从日期中提取月份。

所以，你会做这样的事情：

SELECT DATEPART(month, Closing_Date) AS Closing_Month, COUNT(Status) AS TotalCount
FROM t
GROUP BY DATEPART(month, Closing_Date)

Answer 3:

我用FORMAT函数来实现：

select
 FORMAT(Closing_Date, 'yyyy_MM') AS Closing_Month
 , count(*) cc 
FROM
 MyTable
WHERE
 Defect_Status1 IS NOT NULL
 AND Closing_Date >= '2011-12-01'
 AND Closing_Date < '2016-07-01' 
GROUP BY FORMAT(Closing_Date, 'yyyy_MM')
ORDER BY Closing_Month

Answer 4:

通过增加MONTH(date_column)在GROUP BY 。

SELECT Closing_Date, Category,  COUNT(Status)TotalCount
FROM   MyTable
WHERE  Closing_Date >= '2012-02-01' AND Closing_Date <= '2012-12-31'
AND    Defect_Status1 IS NOT NULL
GROUP BY MONTH(Closing_Date), Category

Answer 5:

DATEPART函数没有在MySQL 5.6的工作，而不是使用MONTH（“2018年1月1日”）

Answer 6:

SELECT  to_char(Closing_Date,'MM'), 
        Category,  
        COUNT(Status) TotalCount 
FROM    MyTable
WHERE   Closing_Date >= '2012-02-01' 
AND     Closing_Date <= '2012-12-31'
AND     Defect_Status1 IS NOT NULL
GROUP BY Category;

Answer 7:

您可以通过使用做好今年（）月（）日（）和日期部分（）。

在您的例子将是：

select  Closing_Date, Category,  COUNT(Status)TotalCount from  MyTable
where Closing_Date >= '2012-02-01' and Closing_Date <= '2012-12-31' 
and Defect_Status1 is not null 
group by Year(Closing_Date), Month(Closing_Date), Category

Answer 8:

试试下面的代码

SELECT  Closing_Date = DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), 
        Category,  
        COUNT(Status) TotalCount 
FROM    MyTable
WHERE   Closing_Date >= '2012-02-01' 
AND     Closing_Date <= '2012-12-31'
AND     Defect_Status1 IS NOT NULL
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Closing_Date), 0), Category;

文章来源: How to group by month from Date field using sql