如此select语句 你会写吗?

[peterddjj]

如此select语句 你会写吗?
小弟遇到个问题,思量许久也的不出答案,希望在这里能得到大家的帮助!!谢先!!!!!!问题如下
tabel
date duration period incarrier inprefix outcarrier outcarrier
91 11 9 a1 qw b1 as
91 12 9 a1 qw b1 as
92 13 9 a1 qw b2 as
92 14 9 a1 qw b2 as
93 15 9 a2 qw b1 as
93 16 9 a2 qw b1 as
94 17 9 a2 qw b2 as
94 18 9 a2 qw b2 as

我想得到下表
table2
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
如何只用一条select语句能得出 table2表

[小辉]

--生成测试数据
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
*/

[xiaoxiao1984]

sys@XIAOXIAO>select t_date, sum(decode(incarrier,'a1',duration,0)) as a1 ,
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

[Eric_1999]

楼主怎么开这么多这个帖子阿？还没解决吗？
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

[peterddjj]

tabel
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