现在一个表中有两列数据
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怎么写啊
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