转换成如下格式简单点：
prod-code
prod_day < 5 00001 1
5=<prod_day <10 00001 1
prod_day >= 10 00001 1
prod_day < 5 00002 1
5=<prod_day <10 00002 0
prod_day >= 10 00002 0
呵呵，但我也知道，这样没有意义
prod-code
prod_day < 5 00001 1
5=<prod_day <10 00001 1
prod_day >= 10 00001 1
prod_day < 5 00002 1
5=<prod_day <10 00002 0
prod_day >= 10 00002 0
呵呵，但我也知道，这样没有意义
select prod_code,sum(seg1),sum(seg2),sum(seg3)
from
(
select prod_code,prod_day as seg1, 0 as seg2,0 as seg3
from a
where prod_day < 5
union
select prod_code,0 as seg1, prod_day as seg2,0 as seg3
from a
where prod_day between 5 and 10
union
select prod_code,0 as seg1, 0 as seg2,prod_day as seg3
from a
where prod_day > 10
)
group by prod_code
--如果分段比较多，sql就比较复杂，运行的速度可能会慢。
create table tb_1(prod_code varchar2(10),prod_day number);
insert into tb_1 values('00001', 2);
insert into tb_1 values('00001' , 6);
insert into tb_1 values('00001' , 15);
insert into tb_1 values('00002' , 4);
select prod_code,sum(case when prod_day <5 then 1 else 0 end) as prod_1,
sum(case when prod_day >=5 and prod_day<10 then 1 else 0 end) as prod_2,
sum(case when prod_day >=10 then 1 else 0 end) as prod_3
from tb_1 group by prod_code;/*
PROD_CODE PROD_1 PROD_2 PROD_3
---------- ---------- ---------- ----------
00001 1 1 1
00002 1 0 0已选择2行。
*/
A1 varchar2(10) ,
a2LessThan5 number default 0,
a2Between5And10 number default 0,
a2MoreThan10 number default 0,
flag number default 0
);insert into test2 (
a1,
a2lessthan5,
flag
)
select
t.a1 ,
count(*) a2LessThan5,
0
from test1 t where a2 < 5 group by a1;
commit;insert into test2 (
a1,
a2between5and10,
flag
)
select
t.a1 ,
count(*) a2Between5And10,
t
from test1 t where a2 >= 5 and a2 < 10 group by a1;
commit;insert into test2 (
a1,
a2morethan10,
flag
)
select
t.a1 ,
count(*) a2MoreThan10,
0
from test1 t where a2 >= 10 group by a1;
commit;insert into test2 (a1,a2lessthan5,a2between5and10,a2morethan10,flag)
select
a1,sum(t.a2lessthan5),sum(t.a2between5and10),sum(t.a2morethan10),1
from test2 t
group by a1;
commit
delete from test2 where flag = 0;
commit;