场景：excel里有10个工号，需要查出每个工号的姓名粘贴到excel。工号和姓名的对应关系存在用户表user。

解决办法一：创建临时表插入数据关联查询

1. 创建临时表，

create table temp(num number,code varchar2(20));

2. 插入数据

select T.* from temp T for update;

复制excel数据到临时表

3. 关联查询

SELECT t.num,t.code,

(select u.name FROM user u where u.code=t.code ) as name

from temp t

order by t.num;

把查询的结果复制到excel。

解决办法二：不建表关联excel数据进行数据库表查询

1. 在excel D列增加公式

增加后显示如下图

其中第1个公式内容如下：

="select "&A2&" as num ,'"&B2&"' as code from dual union all"

2. 关联查询

SELECT t.num,t.code,

(select u.name FROM user u where u.code=t.code ) as name

from

(

select 1 as num ,'10012942' as code from dual union all

select 2 as num ,'10012801' as code from dual union all

select 3 as num ,'10012927' as code from dual union all

select 4 as num ,'10012940' as code from dual union all

select 5 as num ,'10012802' as code from dual union all

select 6 as num ,'10013233' as code from dual union all

select 7 as num ,'10012754' as code from dual union all

select 8 as num ,'10012936' as code from dual union all

select 9 as num ,'10013118' as code from dual union all

select 10 as num ,'10013300' as code from dual

)t

order by t.num;

这个sql与方法一中的sql基本一样，只是其中表名temp的部分，由公式列（D列）复制出来的内容替换掉，注意去掉最后一个union all。

把查询的结果复制到excel。