仔细一个查询命令

Question

"SUM(TRANSAMOUNT)","AREA","AGENCY"
"36314438.1000011","北京","东方航空"
"9139265.70000002","成都","东方航空"
"7859975.00000002","广州","东方航空"
"7725034.30000008","杭州","东方航空"
"125699265.299978","上海","东方航空"
"11158796.2000001","深圳","东方航空"
"738414","北京","海南航空"
"13380","成都","海南航空"
"130246","广州","海南航空"
"62347","杭州","海南航空"
"518646","上海","海南航空"
"232416","深圳","海南航空"
"3284524.3","北京","海南航空B2B"
"14190805","上海","吉祥航空"
"4758280.10000001","北京","山东航空"
"66606.1","广州","山东航空"
"8709483.99999992","上海","山东航空"
"2604190.90000003","北京","上海航空"
"604.4","成都","上海航空"
"4135522.50000005","广州","上海航空"
"47611359.9000017","上海","上海航空"
"3512917.90000002","北京","深圳航空"
"1708979.9","成都","深圳航空"
"2451021.89999999","广州","深圳航空"
"2078714.6","杭州","深圳航空"
"13048753.7000002","上海","深圳航空"
"28852746.9999999","深圳","深圳航空"
"14062319.1000002","成都","四川航空"
"8931683.80000003","上海","四川航空"
"19540839.4999994","上海","厦门航空"
"57398683.6000017","北京","中国国际航空"
"18755186.7","成都","中国国际航空"
"6272719.30000009","广州","中国国际航空"
"13632306.3","杭州","中国国际航空"
"42592077.6000011","上海","中国国际航空"
"8513432.59999999","深圳","中国国际航空"数据库中是这样的表我怎么通过select 查询成如下的表
AirLine 上海 北京 广州 成都 杭州 深圳
东方航空(MU) 1732572.6 518838.35 145682.85 102361.2 116552.35 61566.45
海南航空(HU) 28646.5 44402 6404 2095.5 6687.5 22762.5
吉祥航空(HO) 165703.5 0 0 0 0 0
山东航空(SC) 101640 64248.25 401.2 0 0 0
上海航空(FM) 663190.1 29410.35 108315.65 0 0 0
深圳航空(ZH) 166238.7 32167.35 4729.35 11826.2 22709.9 284809.7
四川航空(3U) 81818.8 0 0 122658.35 0 0
厦门航空(MF) 93587.35 0 0 0 0 0
中国国航(CA) 582062.65 681119.7 54915 243811.5 184242.1 159194.2
东方航空(MU) 1802159.65 515890.05 131148.85 83842 85497.1 162079.05
海南航空(HU) 0 772 0 0 0 0
吉祥航空(HO) 165661 0 0 0 0 0
山东航空(SC) 97250.6 60228.8 916 0 0 0
上海航空(FM) 611583.05 35876.3 105209.85 302.2 0 0
深圳航空(ZH) 234667.65 43752.65 144853.3 24456.65 26304.2 448907.8
四川航空(3U) 99894.45 0 0 99951.1 0 0
厦门航空(MF) 111980.85 0 0 0 0 0
中国国航(CA) 558906.1 706600.45 45878.05 258977.4 213034.35 138874.6

Answer 1

select AGENCY,
       sum(decode(AREA,'上海',sum_TRANSAMOUNT,0)) "上海",
       sum(decode(AREA,'北京',sum_TRANSAMOUNT,0)) "北京",
       sum(decode(AREA,'广州',sum_TRANSAMOUNT,0)) "广州",
       sum(decode(AREA,'成都',sum_TRANSAMOUNT,0)) "成都",
       sum(decode(AREA,'杭州',sum_TRANSAMOUNT,0)) "杭州",
       sum(decode(AREA,'深圳',sum_TRANSAMOUNT,0)) "深圳"
from tb 
group by AGENCY

Answer 2

可能是哪写错了，导致查询没正确匹配。
单独查一下某一个城市，where AREA='上海'

Answer 3

select AGENCY,
       sum(decode(AREA,'上海',sum_TRANSAMOUNT,0)) "上海",
       sum(decode(AREA,'北京',sum_TRANSAMOUNT,0)) "北京",
       sum(decode(AREA,'广州',sum_TRANSAMOUNT,0)) "广州",
       sum(decode(AREA,'成都',sum_TRANSAMOUNT,0)) "成都",
       sum(decode(AREA,'杭州',sum_TRANSAMOUNT,0)) "杭州",
       sum(decode(AREA,'深圳',sum_TRANSAMOUNT,0)) "深圳"
from tb 
group by AGENCY

Answer 4

好的谢谢！ oracle 支持汉字匹配吧！现在是5个城市，能不能自动识别n（n<31）个城市 并罗列出来！ 

Answer 5

with t as(
select 36314438.1000011 sum_TRANSAMOUNT,'北京' AREA,'东方航空' AGENCY from dual
union all
select 9139265.70000002,'成都','上海航空' from dual
union all
select 7859975.00000002,'广州','深圳航空' from dual
union all
select 7725034.30000008,'杭州','上海航空' from dual
union all
select 125699265.299978,'上海','东方航空' from dual
union all
select 11158796.2000001,'深圳','东方航空' from dual
union all
select 738414,'北京','海南航空' from dual
union all
select 13380,'成都','吉祥航空' from dual
union all
select 130246,'广州','海南航空' from dual
) 
select agency,sum(decode(area,'北京',sum_TRANSAMOUNT,0)) 北京,
sum(decode(area,'成都',sum_TRANSAMOUNT,0)) 成都,
sum(decode(area,'广州',sum_TRANSAMOUNT,0)) 广州,
sum(decode(area,'杭州',sum_TRANSAMOUNT,0)) 杭州,
sum(decode(area,'上海',sum_TRANSAMOUNT,0)) 上海,
sum(decode(area,'深圳',sum_TRANSAMOUNT,0)) 深圳 from t group by agency东方航空 36314438.1000011 0 0 0 125699265.299978 11158796.2000001
海南航空 738414 0 130246 0 0 0
吉祥航空 0 13380 0 0 0 0
上海航空 0 9139265.70000002 0 7725034.30000008 0 0
深圳航空 0 0 7859975.00000002 0 0 0
才三十几个，一一列出也不难

Answer 6

--不晓得是不是有空格
--这样试试看看
select AGENCY,
       sum(decode(trim(AREA),'上海',sum_TRANSAMOUNT,0)) "上海",
       sum(decode(trim(AREA),'北京',sum_TRANSAMOUNT,0)) "北京",
       sum(decode(trim(AREA),'广州',sum_TRANSAMOUNT,0)) "广州",
       sum(decode(trim(AREA),'成都',sum_TRANSAMOUNT,0)) "成都",
       sum(decode(trim(AREA),'杭州',sum_TRANSAMOUNT,0)) "杭州",
       sum(decode(trim(AREA),'深圳',sum_TRANSAMOUNT,0)) "深圳"
from tb 
group by AGENCY