Sql 求每个月的总数
求Sql语句 每个月的总数????如:1月15
总数 月份
5 1
5 1
2 1
3 1
53 2
10 2
2 2
3 3
5 4
6 5
10 5
20 6
20 6
53 7
53 7
53 7
53 7
53 7
53 8
53 8
[解决办法]
;with cte(总数,月份) as
(
select 5,1
union all select 5,1
union all select 2,1
union all select 3,1
union all select 53,2
union all select 10,2
union all select 2,2
union all select 3,3
union all select 5,4
union all select 6,5
union all select 10,5
union all select 20,6
union all select 20,6
union all select 53,7
union all select 53,7
union all select 53,7
union all select 53,7
union all select 53,7
union all select 53,8
union all select 53,8
)
select 月份,SUM(总数) as 总数
from cte
group by 月份
/*
月份总数
115
265
33
45
516
640
7265
8106
*/
create table ys(总数 int,月份 int)
insert into ys
select 5,1
union all select 5,1
union all select 2,1
union all select 3,1
union all select 53,2
union all select 10,2
union all select 2,2
union all select 3,3
union all select 5,4
union all select 6,5
union all select 10,5
union all select 20,6
union all select 20,6
union all select 53,7
union all select 53,7
union all select 53,7
union all select 53,7
union all select 53,7
union all select 53,8
union all select 53,8
select 月份,sum(总数) '总数'
from ys
group by 月份
/*
月份 总数
----------- -----------
1 15
2 65
3 3
4 5
5 16
6 40
7 265
8 106
(8 row(s) affected)
*/
select top 1 月份,sum(总数) '总数'
from ys
group by 月份
order by sum(总数) desc
/*
月份 总数
----------- -----------
7 265
(1 row(s) affected)
*/