如何将如下数据按年月日统计，获取统计数据
0
悬赏园豆：30
[已解决问题]
解决于 2013-12-09 16:01
SET NOCOUNT ON SELECT COUNT(1) AS Total, day(InsertTime) as [Day] FROM Count_DataUpload GROUP BY day(InsertTime) SELECT COUNT(1) AS Total, month(InsertTime) AS [Month] FROM Count_DataUpload GROUP BY month(InsertTime) SELECT COUNT(1) AS Total, year(InsertTime) AS [Year] FROM Count_DataUpload GROUP BY year(InsertTime)
显示结果：
Total Day
----------- -----------
45 20
Total Month
----------- -----------
45 11
Total Year
----------- -----------
45 2013
有没有办法把这3个查询转换成
Year YearTotal Month MonthTotal Day DayTotal
2013 45 11 45 20 45
最佳答案
0
SELECT distinct year(InsertTime) AS [Year],COUNT(1) over(partition by year(inserttime)) AS YearTotal ,month(InsertTime) AS [Month],COUNT(1) over(partition by month(inserttime)) AS MonthTotal, day(InsertTime)as [Day],COUNT(1) over(partition by day(inserttime)) AS DayTotal FROM Count_DataUpload
或者 你想要的不就是直接union起来不就好了吗 简化的方法是使用group by grouping sets
SELECT year(InsertTime) AS [Year],COUNT(1) AS YearTotal, month(InsertTime) AS [Month],COUNT(1) AS MonthTotal, day(InsertTime)as [Day],COUNT(1) AS DayTotal FROM Count_DataUpload GROUP BY grouping sets ( year(InsertTime), MONTH(inserttime), DAY(inserttime) )
收获园豆：20
其他回答(1)
0
SELECT year(InsertTime) AS [Year],COUNT(1) AS YearTotal,month(InsertTime) AS [Month],COUNT(1) AS MonthTotal,day(InsertTime) as [Day],COUNT(1) AS DayTotal FROM Count_DataUpload GROUP BY year(InsertTime);
收获园豆：10
