请问怎么实现这个功能?

[s_hluo]

假如我有这样一个表, 有两列: A, B. 都为int型

A B
1 4
3 6
4 6
...

我想实现统计A列, 统计条件是 0=< C<=1 , 3=<D<=4, 现在要统计A列中符合C, D条件的值, 并把符合C,D条件对应的B列和求出来, 结果类似如下:
result r_sum
1 4
2 12


请问应该怎么实现? SQL语句应该怎么写? 谢谢.

[caixia615]

select result=1,r_sum=sum(b) from tablename where a between 1 and 2
union all
select result=2,r_sum=sum(b) from tablename where a between 3 and 4

[fellowcheng]

一楼

[caixia615]

select result=1,r_sum=sum(b) from tablename where a between 0 and 1
union all
select result=2,r_sum=sum(b) from tablename where a between 3 and 4

[s_hluo]

select result=1,r_sum=sum(b) from tablename where a between 0 and 1
~~~~~~~~这个不能写死, 因为它也是根据条件计算出来的.
union all
select result=2,r_sum=sum(b) from tablename where a between 3 and 4

[caixia615]

select result=(case when a between 0 and 1 then 1 end),r_sum=sum(b) from tablename where a between 0 and 1
~~~~~~~~这个不能写死, 因为它也是根据条件计算出来的.
union all
select result=(case when a between 3 and 4 then 2 end),r_sum=sum(b) from tablename where a between 3 and 4

[akuzou]

楼上的语法估计有问题，你建个表试试看

[caixia615]

select result,sum(b) as r_sum from(select result=(case when a between 0 and 1 then 1 end),b from @a where a between 0 and 1)a
group by result
union all
select result,sum(b) as r_sum from(select result=(case when a between 3 and 4 then 2 end),b from @a where a between 3 and 4)b
group by result

[hrb133yqq]

create table table2(col1 int, col2 int)

insert into table2(col1,col2)
select 1, 4 union all
select 3, 6 union all
select 4, 6


select count(col1) as a ,sum(col2) as b from table2 where col1 between 1 and 2
union
select count(col1),sum(col2) from table2 where col1 between 3 and 4

[中国风]

用表变量@ta测试:
declare @ta table (A int, B int)
insert @ta
select 1, 4
union all select 3, 6
union all select 4, 6

select a=case when a between 1 and 2 then 1 when a between 3 and 4 then 2 end,
b=sum(b)
from @ta
group by case when a between 1 and 2 then 1 when a between 3 and 4 then 2 end

（所影响的行数为 3 行）

a b
----------- -----------
1 4
2 12

（所影响的行数为 2 行）

[中国风]

用表变量@ta测试:
--把表变量@ta改为表名就行了
declare @ta table (A int, B int)
insert @ta
select 1, 4
union all select 3, 6
union all select 4, 6
select result=case when a between 1 and 2 then 1 when a between 3 and 4 then 2 end,
r_sum=sum(b)
from @ta
group by case when a between 1 and 2 then 1 when a between 3 and 4 then 2 end

（所影响的行数为 3 行）

result r_sum
----------- -----------
1 4
2 12

（所影响的行数为 2 行）

[Well]

union all进行联合

[InFerNaL_LioN]

create table #temp
(
A int,
B int
)

insert into #temp values(1,4)
insert into #temp values(3,6)
insert into #temp values(4,6)


select 1,
(
select sum(B) total from #temp
where A>=0
and A<=1
)
union all
select 2,
(
select sum(B) total from #temp
where A>=3
and A<=4
)

[marco08]

create table T(A int, B int)
insert T select 1, 4
union all select 3, 6
union all select 4, 6

select result=1, r_sum=sum(B) from T
where A between 0 and 1
union all
select 2, sum(B) from T
where A between 3 and 4

--result
result r_sum
----------- -----------
1 4
2 12

(2 row(s) affected)

[borcas2010]

hrb133yqq() ( ) 信誉：100 Blog 2006-12-31 16:09:55 得分: 0



create table table2(col1 int, col2 int)

insert into table2(col1,col2)
select 1, 4 union all
select 3, 6 union all
select 4, 6


select count(col1) as a ,sum(col2) as b from table2 where col1 between 1 and 2
union
select count(col1),sum(col2) from table2 where col1 between 3 and 4



这好象才是楼主想要的：）