Group By Month子句问题

问题描述：

我有一个SQL查询来按月分组添加到系统中的多个产品。我上周跑过这个查询，今天又跑了。今天的结果似乎有不同的顺序。是否可以运行这个查询，并有第二列显示月份？Group By Month子句问题

这是我原来的查询：

SELECT COUNT(*) 
FROM dbo.Products p 
WHERE YEAR(t.CreatedDate) = '2013' 
    AND t.Deleted = 0 
    AND t.RemovedFromSale = 0 
GROUP BY MONTH(t.CreatedDate)

这将返回以下：理想

| (No Column Name) 
1| 2009 
2| 161 
3| 98

，我想是这样的：

| (No Column Name) | Month Name 
1| 2009    | 
2| 161    | 
3| 98    |

这是可能通过改变我的查询？

答

如果你想要回MONTH(t.CreatedDate)，那么你就需要在你的SELECT列表中的列：

SELECT COUNT(*) as Total, 
    MONTH(t.CreatedDate) as Month 
FROM dbo.Products p 
WHERE YEAR(t.CreatedDate) = '2013' 
    AND t.Deleted = 0 
    AND t.RemovedFromSale = 0 
GROUP BY MONTH(t.CreatedDate)

的MONTH()将返回每月一个整数。如果你想返回月份，因为它的名字，那么你将要使用DATENAME()

SELECT COUNT(*) as Total, 
    DATENAME(month, t.CreatedDate) as Month 
FROM dbo.Products p 
WHERE YEAR(t.CreatedDate) = '2013' 
    AND t.Deleted = 0 
    AND t.RemovedFromSale = 0 
GROUP BY DATENAME(month, t.CreatedDate)

答

更改SQL以

SELECT COUNT(*), MONTH(t.CreatedDate) 
    FROM dbo.Products p 
    WHERE YEAR(t.CreatedDate) = '2013' AND t.Deleted = 0 AND t.RemovedFromSale = 0 
    GROUP BY MONTH(t.CreatedDate)

答

尝试：

SELECT 
    COUNT(*), DATENAME(m, CreatedDate) [MonthName] 
FROM dbo.Products p 
WHERE YEAR(t.CreatedDate) = '2013' AND t.Deleted = 0 AND t.RemovedFromSale = 0 
GROUP BY DATENAME(m, CreatedDate)

Group By Month子句问题

相关推荐