--生成测试数据
create table
testGroup
(
date int ,
duration int ,
incarrier varchar(5) ,
outcarrier varchar(5),
)insert into testGroup
select 91 , 11 ,'a1','b1' union
select 91 , 12 ,'a1','b1' union
select 92 , 13 ,'a1','b2' union
select 92 , 14 ,'a1','b2' union
select 93 , 15 ,'a2','b1' union
select 93 , 16 ,'a2','b1' union
select 94 , 17 ,'a2','b2' union
select 94 , 18 ,'a2','b2'
--执行查询
select
a.date ,
(case a.incarrier when 'a1' then a.sm else 0 end) as incarrier_a1,
(case a.incarrier when 'a2' then a.sm else 0 end) as incarrier_a2,
(case a.outcarrier when 'b1' then a.sm else 0 end) as outcarrier_b1,
(case a.outcarrier when 'b2' then a.sm else 0 end) as outcarrier_b2
from
(
select date , incarrier , outcarrier , sum(duration) as sm
from testGroup
group by date , incarrier , outcarrier
) a/*
date incarrier_a1 incarrier_a2 outcarrier_b1 outcarrier_b2
-----------------------------------------------------------
91 23 0 23 0
92 27 0 0 27
93 0 31 31 0
94 0 35 0 35
*/
create table
testGroup
(
date int ,
duration int ,
incarrier varchar(5) ,
outcarrier varchar(5),
)insert into testGroup
select 91 , 11 ,'a1','b1' union
select 91 , 12 ,'a1','b1' union
select 92 , 13 ,'a1','b2' union
select 92 , 14 ,'a1','b2' union
select 93 , 15 ,'a2','b1' union
select 93 , 16 ,'a2','b1' union
select 94 , 17 ,'a2','b2' union
select 94 , 18 ,'a2','b2'
--执行查询
select
a.date ,
(case a.incarrier when 'a1' then a.sm else 0 end) as incarrier_a1,
(case a.incarrier when 'a2' then a.sm else 0 end) as incarrier_a2,
(case a.outcarrier when 'b1' then a.sm else 0 end) as outcarrier_b1,
(case a.outcarrier when 'b2' then a.sm else 0 end) as outcarrier_b2
from
(
select date , incarrier , outcarrier , sum(duration) as sm
from testGroup
group by date , incarrier , outcarrier
) a/*
date incarrier_a1 incarrier_a2 outcarrier_b1 outcarrier_b2
-----------------------------------------------------------
91 23 0 23 0
92 27 0 0 27
93 0 31 31 0
94 0 35 0 35
*/
2 sum(decode(incarrier,'a2',duration,0)) as a2,
3 sum(decode(outcarrier,'b1',duration,0)) as b1,
4 sum(decode(outcarrier,'b2',duration,0)) as b2
5 from testc
6 group by t_date; T_DATE A1 A2 B1 B2
---------- ---------- ---------- ---------- ----------
91 23 0 23 0
92 27 0 0 27
93 0 31 31 0
94 0 35 0 35
date duration period incarrier inprefix outcarrier outprefix
91 11 9 a1 a b1 a
91 12 9 a1 a b1 a
92 13 9 a1 a b1 a
92 14 9 a1 b b2 a
93 15 9 a2 b b2 c
93 16 9 a2 b b2 c
94 17 9 a2 a b2 a
94 18 9 a2 a b1 a
我想的到下表 select语句该怎么写? incarrier_a1就是当inprefix为a时 a1每天duration的总量
incarrier_a2就是当inprefix为a时 a2每天duration的总量
outcarrier_b1就是当outprefix为a时 b1每天duration的总量
outcarrier_b2就是当outprefix为a时 b2每天duration的总量
table2
date incarrier_a1 incarrier_a2 outcarrier_b1 outcarrier_b2
91 23 0 23 0
92 13 0 13 14
93 0 0 0 0
94 0 35 18 17
select date,
sum(decode(inprefix, a, decode(incarrier, a1, duration,0), 0)) incarrier_a1,
sum(decode(inprefix, a, decode(incarrier, a2, duration,0), 0)) incarrier_a,
sum(decode(outprefix, a, decode(outcarrier, b1, duration,0), 0)) outcarrier_b1,
sum(decode(outprefix, a, decode(outcarrier, b1, duration,0), 0)) outcarrier_b1
from tmp
group by date