SQL语句-按分类3条记录总和
SQL语句--按分类3条记录求一个总和
格式如下:
ID value type
1 1 A
2 2 A
3 3 B
4 4 A
5 5 B
6 6 C
7 7 B
8 8 C
9 9 A
10 10 A
11 11 C
12 12 A
我想实现按分类,每隔3条记录求一次总和,例如A类:
1+2+4=7 A
9+10+12=31 A
希望得到大家的帮助,非常感谢大家
------解决方案--------------------
------解决方案--------------------
------解决方案--------------------
按照3个一分组啊 不是3个的排除了。
格式如下:
ID value type
1 1 A
2 2 A
3 3 B
4 4 A
5 5 B
6 6 C
7 7 B
8 8 C
9 9 A
10 10 A
11 11 C
12 12 A
我想实现按分类,每隔3条记录求一次总和,例如A类:
1+2+4=7 A
9+10+12=31 A
希望得到大家的帮助,非常感谢大家
------解决方案--------------------
-- id value type
;with cte as
(
select *,
growid = row_number() over (partition by type order by id)
from tb
)
select value,(growid-1)/3 as grow,sum(value) as total
from cte
group by value,(growid-1)/3
------解决方案--------------------
create table gu
(ID int,value int,[type] varchar(5))
insert into gu
select 1,1,'A' union all
select 2,2,'A' union all
select 3,3,'B' union all
select 4,4,'A' union all
select 5,5,'B' union all
select 6,6,'C' union all
select 7,7,'B' union all
select 8,8,'C' union all
select 9,9,'A' union all
select 10,10,'A' union all
select 11,11,'C' union all
select 12,12,'A'
select [type],sum(value) '总和'
from
(select *,(row_number() over(partition by [type] order by ID)-1)/3 'rn'
from gu) t
group by [type],rn
having count(1)=3
/*
type 总和
----- -----------
A 7
B 15
C 25
A 31
(4 row(s) affected)
*/
------解决方案--------------------
按照3个一分组啊 不是3个的排除了。