日期:2014-05-17 浏览次数:20767 次
你在修改一下就可以了,方法就是这样 --测试数据 create table t2(Col1 int,Col2 int,Col3 int) insert into t2 select -1,-1,-1 from dual union all select -1,-1,0 from dual union all select -1,0,0 from dual union all select -1,-1,null from dual union all select -1,null,0 from dual; --执行查询 select substr(t.s,1,length(s)-1) from (select decode(col1,-1,col1||'\','')||decode(col2,-1,col2||'\','')||decode(col3,-1,col3||'\','') s from t2 )t --查询结果 1 -1\-1\-1 2 -1\-1 3 -1 4 -1\-1 5 -1
------解决方案--------------------
就一句话,还不用子查询
原始表
select * from [tablename] d;
-1 -1 0
-1 0 null
0 -1 null
-1 null null
-1 -1 -1
结果:
Select d.*,substr(Case d.Col1 When -1 Then '/部门1 'End ¦ ¦ Case d.Col2 When -1 Then '/部门2 'End ¦ ¦ Case d.Col3 When -1 Then '/部门3 'End,2) From [tablename] d;
-1 -1 0 部门1/部门2
-1 0 null 部门1
0 -1 null 部门2
-1 null null 部门3
-1 -1 -1 部门1/部门2/部门3