现在一个表中有两列数据
shuliang area
1 025
2 0510
3 0511
4 0512
5 0513
要求是把area字段中的值转换成城市名称,比如025是南京,0510是无锡,0511是镇江,0512是苏州,0512是南通
显示成
南京 无锡 镇江 苏州 南通
1 2 3 4 5请问sql怎么写啊
shuliang area
1 025
2 0510
3 0511
4 0512
5 0513
要求是把area字段中的值转换成城市名称,比如025是南京,0510是无锡,0511是镇江,0512是苏州,0512是南通
显示成
南京 无锡 镇江 苏州 南通
1 2 3 4 5请问sql怎么写啊
解决方案 »
- 求教:关于表空间管理维护的问题
- oracle中imp命令详解
- orcale中怎样去判断创建的一个DBLINK已经连接成功?
- 我的plsql dev为什么不能和vpn一起用?
- 谁有 The oracle 9.2.0.6 patch 呀
- 求救,创建触发器失败?
- Jbuilder2005+Oracle9i,配置完驱动,使用Database Piolt连接数据库时报错如下:
- 求sql语句!
- oracle里面能否识别001|002|003|.....这种类型呢?
- oracle装好了后,net8和oracle database assistant都打不开,该怎么办?
- 想下载一个Oracle10G的
- 取当月第一天的0点0分0秒及23点59分59秒
select sum(case when area = '025' then shuliang else 0 end) as 南京
,sum(case when area = '0510' then shuliang else 0 end) as 无锡
,sum(case when area = '0511' then shuliang else 0 end) as 镇江
,sum(case when area = '0512' then shuliang else 0 end) as 苏州
,sum(case when area = '0513' then shuliang else 0 end) as 南通
from 表名;
case when area='025' then shuliang end 南京,
case when area='0510' then shuliang end 无锡,
case when area='0511' then shuliang end 镇江,
case when area='0512' then shuliang end 苏州,
case when area='0513' then shuliang end 南通
from 表;
sum(decode(area, '0510', 1, 0)) as 无锡,
sum(decode(area, '0511', 1, 0)) as 镇江,
sum(decode(area, '0512', 1, 0)) as 苏州,
sum(decode(area, '0513', 1, 0)) as 南通
from t
group by area
case when area = '025' then '无锡',
case when area = '025' then '镇江',
case when area = '025' then '苏州',
case when area = '025' then '南通' from your_table
union all
select case when area = '025' then '1',
case when area = '025' then '2',
case when area = '025' then '3',
case when area = '025' then '4',
case when area = '025' then '5' from your_table
把1 2 3 4 5 改成 shuliang
呵呵
SHULIANG AREA
---------- ----------
1 25
2 510
3 511
4 512
5 513
SQL> SELECT SUM(decode(area, 025, shuliang, 0)) AS 南京
2 ,SUM(decode(area, 0510, shuliang, 0)) AS 无锡
3 ,SUM(decode(area, 0511, shuliang, 0)) AS 镇江
4 ,SUM(decode(area, 0512, shuliang, 0)) AS 苏州
5 ,SUM(decode(area, 0513, shuliang, 0)) AS 南通
6 FROM t
7 ;
南京 无锡 镇江 苏州 南通
---------- ---------- ---------- ---------- ----------
1 2 3 4 5
在使用DECODE函数时需要注意,area后面的值要和列area的类型相同。如果你的area是字符型,那么就在那些区号两边加上单引号,例如025就变成'025'。
SUM(decode(area, '0510', shuliang, 0)) AS 无锡
SUM(decode(area, '0511', shuliang, 0)) AS 镇江
SUM(decode(area, '0512', shuliang, 0)) AS 苏州
SUM(decode(area, '0513', shuliang, 0)) AS 南通
FROM tb