我的思路:
select (select sum(value) from a where id<=2) 'a1',
(select sum(value) from a where id>2 and id<=4) 'a2',
(select sum(value) from a where id>4 and id<=5) 'a3',
(select sum(value) from a where id>5) 'a4'
from a
select (select sum(value) from a where id<=2) 'a1',
(select sum(value) from a where id>2 and id<=4) 'a2',
(select sum(value) from a where id>4 and id<=5) 'a3',
(select sum(value) from a where id>5) 'a4'
from a
实际上我就是想把表a的数据分段求和,分段的方法以表b为准
select 结果=sum(case when id<=2 then Value else 0 end) from 表a
union all sum(case when id<=4 and id>2 then value else 0 end) from 表a
union all sum(case when id<=5 and id>4 then value else 0 end) from 表a
union all sum(case when id>5 then value else 0 end) from 表a
create table a(id int,value numeric(4,1))
create table b(section int)insert into a select 1,0.3
insert into a select 2,12
insert into a select 3,2
insert into a select 4,8.6
insert into a select 5,7.1
insert into a select 6,1.1
insert into a select 7,0.1insert into b select 2
insert into b select 4
insert into b select 5
--执行查询
select section,identity(int,1,1) as id into #t from bselect
t.section,
value = sum(a.value)
from
a,
(select
isnull(rtrim(ta.section),'')+'--'+isnull(rtrim(tb.section),'') as section,
ta.section as section1,
tb.section as section2
from
#t ta
full outer join
#t tb
on
ta.id+1 = tb.id ) t
where
a.id > isnull(t.section1,0) and a.id <= isnull(t.section2,999)
group by
t.section,t.section1,t.section2
create table a(id int,value numeric(4,1))
create table b(section int)insert into a select 1,0.3
insert into a select 2,12
insert into a select 3,2
insert into a select 4,8.6
insert into a select 5,7.1
insert into a select 6,1.1
insert into a select 7,0.1insert into b select 2
insert into b select 4
insert into b select 5
--执行查询
select section,identity(int,1,1) as id into #t from bselect
value = sum(a.value)
from
a,
(select
ta.section as section1,
tb.section as section2
from
#t ta
full outer join
#t tb
on
ta.id+1 = tb.id ) t
where
a.id > isnull(t.section1,0) and a.id <= isnull(t.section2,999)
group by
t.section1,t.section2
order by
isnull(t.section1,0)--执行结果
value
-------
12.3
10.6
7.1
1.2
--如果表b中的section是无序的,那么用以下查询生成临时表,而输出数据的查询则不需要变动
select t.section,identity(int,1,1) as id into #t from (select section from b order by section)
create table a(id int,value numeric(4,1))
create table b(section int)insert into a select 1,0.3
insert into a select 2,12
insert into a select 3,2
insert into a select 4,8.6
insert into a select 5,7.1
insert into a select 6,1.1
insert into a select 7,0.1insert into b select 2
insert into b select 4
insert into b select 5
go--统计
select section=null,value=(select sum(value) from a where id<=(select min(section) from b))
union all
select b.section,sum(a.value)
from a,(select section from b union all select null)b
where a.id>b.section and a.id<=isnull((select min(section) from b bb where bb.section>b.section),a.id)
group by b.section
go
drop table a,b/*--结果
section value
----------- ----------------------------------------
NULL 12.3
2 10.6
4 7.1
5 1.2
--*/