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

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