想通过SQL语句实现如下结果,
数值区间 [-30, -25) [-25, -20) [-20, -15) [-15, -10) [-10, -5)
求和数值 77.48777008 376.756355286 453.50503993 543.089648724 127.003311157假设数据表Table如下
A B
-29 15.2323754
-23 1.2443533
-27 15.234324
-7 10.876234
-14 5.22836734
-21 115.2323124
-5 152.212334
.......请大侠帮忙指点下如何写出sql语句实现按照A的数值区间分组统计求和?谢谢SQLOracle
数值区间 [-30, -25) [-25, -20) [-20, -15) [-15, -10) [-10, -5)
求和数值 77.48777008 376.756355286 453.50503993 543.089648724 127.003311157假设数据表Table如下
A B
-29 15.2323754
-23 1.2443533
-27 15.234324
-7 10.876234
-14 5.22836734
-21 115.2323124
-5 152.212334
.......请大侠帮忙指点下如何写出sql语句实现按照A的数值区间分组统计求和?谢谢SQLOracle
when tc.csdn_id > -30 and tc.csdn_id <= -25 then
'[-30, -25)'
when tc.csdn_id > -25 and tc.csdn_id <= -20 then
'[-25, -20)'
when tc.csdn_id > -20 and tc.csdn_id <= -15 then
'[-20, -15)'
when tc.csdn_id > -15 and tc.csdn_id <= -10 then
'[-15, -10)'
when tc.csdn_id > -10 and tc.csdn_id <= -5 then
'[-10, -5)'
end 区间,
sum(tc.csdn_num) 求和
from temp_csdn tc
group by case
when tc.csdn_id > -30 and tc.csdn_id <= -25 then
'[-30, -25)'
when tc.csdn_id > -25 and tc.csdn_id <= -20 then
'[-25, -20)'
when tc.csdn_id > -20 and tc.csdn_id <= -15 then
'[-20, -15)'
when tc.csdn_id > -15 and tc.csdn_id <= -10 then
'[-15, -10)'
when tc.csdn_id > -10 and tc.csdn_id <= -5 then
'[-10, -5)'
end
建表语句create table temp_csdn(
csdn_id number,
csdn_num number(11,2)
)
insert into temp_csdn values(-29,15.23);
insert into temp_csdn values(-23,1.24);
insert into temp_csdn values(-27,15.23);
insert into temp_csdn values(-7,10.87);
insert into temp_csdn values(-14,5.23);
insert into temp_csdn values(-21,115.23);
insert into temp_csdn values(-5,152.21);