SQL玩出花--多个SQL查询合并为一个

之前有讲过使用decode来做表查询的合并。表连接部分相同但是连接条件不同的时候，既可以使用union all将多个查询拼接起来，也可以在一个查询中使用decode选择性地连接相应的记录。没有实例属实有点抽象。还是有请Oracle老演员emp表出厂，见下方。

假如我有需求：

字段1：码值为ABC

字段2：A对应的是部门10，job为PRESIDENT员工姓名。B对应部门20，job为MANAGER的员工姓名，C对应部门30，job为‘CLERK’的员工姓名

算法1：

简单的表拼接

select 'A',(select ename from emp where  job='PRESIDENT' and deptno=10) from dual
union
select 'B',(select ename  from emp where job='MANAGER' and deptno=20) from dual
union
select 'C',(select ename  from emp  where job='CLERK' and deptno=30) from dual

算法2：

表部分相同，但是条件不同，可以考虑表连接里面做判断

select T1.c,T2.ename from
(select 'A' c from dual
union
select 'B' c from dual
union
select 'C' c from dual)T1
join emp T2 on deptno=decode(T1.c,'A',10,'B',20,'C',30) and  job=decode(T1.c,'A','PRESIDENT','B','MANAGER','C','CLERK')

验证：

select 'A',(select ename from emp where  job='PRESIDENT' and deptno=10) from dual
union
select 'B',(select ename  from emp where job='MANAGER' and deptno=20) from dual
union
select 'C',(select ename  from emp  where job='CLERK' and deptno=30) from dual

minus

select T1.c,T2.ename from
(select 'A' c from dual
union
select 'B' c from dual
union
select 'C' c from dual)T1
join emp T2 on deptno=decode(T1.c,'A',10,'B',20,'C',30) and  job=decode(T1.c,'A','PRESIDENT','B','MANAGER','C','CLERK')

结果为空集。