返回信息流Year region sales
2003 AP 1500
2003 AG 2000
2004 AG 2000
2005 AP 3500
2005 AG 1700
2006 AP 2300
转化成
Year 2003 2004 2005 2006
AP 1500 0 3500 2300
AG 2000 2000 1700 0
思考了半天也没写出来。
请教一下大家，谢谢！！
这是一条镜像帖。来源：北邮人论坛 / database / #3581同步于 2009/6/9
该镜像源已超过 30 天没有更新，可能在源站已被删除。
Database机器人发帖
请教一个sql语句的写法
telnet
2009/6/9镜像同步8 回复
订阅后，新回复会通过你的通知中心匿名送达。
8 条回复
没看明白逻辑关系....
【 在 telnet (攀冰峰的倒霉熊) 的大作中提到: 】
: Year region sales
: 2003 AP 1500
: 2003 AG 2000
: ...................
/*
Year region sales
2003 AP 1500
2003 AG 2000
2004 AG 2000
2005 AP 3500
2005 AG 1700
2006 AP 2300
转化成
Year 2003 2004 2005 2006
AP 1500 0 3500 2300
AG 2000 2000 1700 0
*/
create table test
(
year varchar2(5),
region varchar2(5),
sales number(5)
);
insert into test values('2003','AP',1500);
insert into test values('2003','AG',2000);
insert into test values('2004','AG',2000);
insert into test values('2005','AP',3500);
insert into test values('2005','AG',1700);
insert into test values('2006','AP',2300);
commit;
select *
from test;
select 'AP' as "year",
(select nvl(sum(nvl(sales,0)),0) from test where year = '2003' and region='AP') as "2003",
(select nvl(sum(nvl(sales,0)),0) from test where year = '2004' and region='AP') as "2004",
(select nvl(sum(nvl(sales,0)),0) from test where year = '2005' and region='AP') as "2005",
(select nvl(sum(nvl(sales,0)),0) from test where year = '2006' and region='AP') as "2006"
from dual
union all
select 'AG' as "year",
(select nvl(sum(nvl(sales,0)),0) from test where year = '2003' and region='AG') as "2003",
(select nvl(sum(nvl(sales,0)),0) from test where year = '2004' and region='AG') as "2004",
(select nvl(sum(nvl(sales,0)),0) from test where year = '2005' and region='AG') as "2005",
(select nvl(sum(nvl(sales,0)),0) from test where year = '2006' and region='AG') as "2006"
from dual
【 在 telnet 的大作中提到: 】
: Year region sales
: 2003 AP 1500
: 2003 AG 2000
: ...................
orz...
oracle么......
【 在 wuquehua (花无缺之伴一船风月，乘千里烟浪) 的大作中提到: 】
: /*
: Year region sales
: 2003 AP 1500
: ...................
PS:
竖着可以么...
select * from (
select year from test group by year
) as Y left join (
select sales as AP,year from test where region='AP'
) as P using(year) left join (
select sales as AG,year from test where region='AG'
) as G using(year)
【 在 dickfu (潇湘天下|小付寨主) 的大作中提到: 】
: orz...
: oracle么......