id 月 数值
1 1 5
1 1 5
1 2 6
1 2 6
1 3 8
1 3 8
2 1 4
2 1 4
2 2 7
2 2 7
2 3 9
2 3 9
-----------------
要求每月只要一条数据,就是把两个一样的月合并成一条数据计算
期望结果
1 的数值总和
2 的数值总和
1 1 5
1 1 5
1 2 6
1 2 6
1 3 8
1 3 8
2 1 4
2 1 4
2 2 7
2 2 7
2 3 9
2 3 9
-----------------
要求每月只要一条数据,就是把两个一样的月合并成一条数据计算
期望结果
1 的数值总和
2 的数值总和
from tablename
group by 月
insert into @T select 1 ,1 ,5
union all select 1 ,1 ,5
union all select 1 ,2 ,6
union all select 1 ,2 ,6
union all select 1 ,3 ,8
union all select 1 ,3 ,8
union all select 2 ,1 ,4
union all select 2 ,1 ,4
union all select 2 ,2 ,7
union all select 2 ,2 ,7
union all select 2 ,3 ,9
union all select 2 ,3 ,9
select 月,sum(数值) as 数值 from @t group by 月???
from 表名
group by 年,月
order by 年,月
from tablename
group by id, 月
insert into @T select 1 , 2004,1 ,5
union all select 1 ,2004 ,1 ,5
union all select 1 ,2005 ,2 ,6
union all select 1 ,2004 ,2 ,6
union all select 1 ,2004 ,3 ,8
union all select 1 ,2004 ,3 ,8
union all select 2 ,2005 ,1 ,4
union all select 2 ,2005 ,1 ,4
union all select 2 ,2005 ,2 ,7
union all select 2 ,2004 ,2 ,7
union all select 2 ,2004 ,3 ,9
union all select 2 ,2005 ,3 ,9
select 年,月,sum(数值) as 数值 from @t group by 年,月 order by 年,月
??
select id, sum(数值) as 数值
from (select distinct * from 表) a
group by id
---------------------------------------------------------
如果有的话,可以这样:
select 年,月,sum(数值) as 数值
from 表
group by 年,月
select 1 as id, 1 as 月, 5 as 数值
into tablename
union all select 1 ,1 ,5
union all select 1 ,2 ,6
union all select 1 ,2 ,6
union all select 1 ,3 ,8
union all select 1 ,3 ,8
union all select 2 ,1 ,4
union all select 2 ,1 ,4
union all select 2 ,2 ,7
union all select 2 ,2 ,7
union all select 2 ,3 ,9
union all select 2 ,3 ,9
-----------------------------------
select id, sum(数值) as 数值
from (select distinct * from tablename) a
group by id
/*
id 数值
1 19
2 20
*/
-----------------------------------
drop table tablename
select 1 as id, 1 as 月, 5 as 数值
into tablename
union all select 1 ,1 ,5
union all select 1 ,2 ,6
union all select 1 ,2 ,6
union all select 1 ,3 ,8
union all select 1 ,3 ,8
union all select 2 ,1 ,4
union all select 2 ,1 ,4
union all select 2 ,2 ,7
union all select 2 ,2 ,7
union all select 2 ,3 ,9
union all select 2 ,3 ,9
-----------------------------------
select id, sum(数值) as 数值
from (select id, 月, min(数值) as 数值--或者这样,min可以根据需要换成max等
from tablename
group by id, 月) a
group by id
/*
id 数值
1 19
2 20
*/
-----------------------------------
drop table tablename
create table a(id int ,year int, month int ,sz int)
insert into a
select 1,1,5
union all select 1,1,1,5
union all select 1,1,2,6
union all select 1,1,2,6
select year,month ,sum(sz) from a group by year,month order by month