如何将如下数据按年月日统计，获取统计数据

Question

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

Answer 1

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) )