首页 新闻 赞助 找找看

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

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

最佳损友。的主页 最佳损友。 | 初学一级 | 园豆:63
提问于:2013-11-20 18:06
< >
分享
最佳答案
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
tneduts | 菜鸟二级 |园豆:239 | 2013-11-21 07:57
其他回答(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
yuankess | 园豆:252 (菜鸟二级) | 2013-11-20 18:37
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册