求脚本

Question

若a表中的next_id这个字段是文本类型的，值是 "100,101,103,104" 
这个id关联的是表employee里面的employee_id, employee包括employee_id和employee_name, 请教如何将a表中所有记录的next_id字段转换成 employee_name？ 谢谢

Answer 1

update a 
set next_id=(select employee_name from employee e where e.employee_id=a.next_id and rownum=1)
where exists(select 1 from employee where employee_id=a.next_id)
 

Answer 2

行转列，转换成中文，再列转行，代码如下，由于使用了正则表达式，需要oracle 10g以上版本：with a as
 (select '100,101,103,104' next_id from dual),
employee as
 (select '100' employee_id, '一百' employee_name
    from dual
  union all
  select '101', '一百零一'
    from dual
  union all
  select '103', '一百零三'
    from dual
  union all
  select '104', '一百零四' from dual)
  
select wm_concat(tb2.employee_name)
  from (select replace(regexp_substr(next_id, ',?\d+,?', 1, level), ',') employee_id
          from a
        connect by level <= length(regexp_replace(next_id, ',?\d+,?', '@'))) tb1,
       employee tb2
 where tb1.employee_id = tb2.employee_id;

Answer 3

----------这个用于每个id长度一定的情况，我演示的为id长度为4的,你可以直接把5改成3就行了
SQL> with a as
  2  (
  3    select '7369,7499,7521,7566' id from dual
  4  )
  5  select *
  6    from scott.emp t
  7   where t.empno in
  8         (select rtrim(ltrim(substr(id, 5 * (rownum - 1), 5), ','), ',') newid
  9            from a
 10          connect by rownum <= length(id) - length(replace(id, ',', '')) + 1)
 11  ;EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00    200.00     20
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30SQL> 

Answer 4

with a as
(
  select '7369,7499,7521,7566' id from dual
)
select t.empno,t.ename
  from scott.emp t
 where t.empno in
       (select rtrim(ltrim(substr(id, 5 * (rownum - 1), 5), ','), ',') newid
          from a
        connect by rownum <= length(id) - length(replace(id, ',', '')) + 1)

Answer 5

SQL> select * from emp;
 
                EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
--------------------- ---------- --------- ----- ----------- --------- --------- ------
                 7369 SMITH      CLERK      7902 1980-12-17     968.12               20
                 7499 ALLEN      SALESMAN   7698 1981-2-20     1936.00    300.00     30
                 7521 WARD       SALESMAN   7698 1981-2-22     1512.50    500.00     30
                 7566 JONES      MANAGER    7839 1981-4-2      3599.75               20
                 7654 MARTIN     SALESMAN   7698 1981-9-28     1512.50   1400.00     30
                 7698 BLAKE      MANAGER    7839 1981-5-1      3448.50               30
                 7782 CLARK      MANAGER    7839 1981-6-9      2964.50               10
                 7788 SCOTT      ANALYST    7566 1987-4-19     3630.00               20
                 7839 KING       PRESIDENT       1981-11-17    6050.00               10
                 7844 TURNER     SALESMAN   7698 1981-9-8      1815.00      0.00     30
                 7876 ADAMS      CLERK      7788 1987-5-23     1331.00               20
                 7900 JAMES      CLERK      7698 1981-12-3     1149.50               30
                 7902 FORD       ANALYST    7566 1981-12-3     3630.00               20
                 7934 MILLER     CLERK      7782 1982-1-23     1573.00               10
 
14 rows selected
 
SQL> select * from t_con;
 
        ID NAME
---------- --------------
         1 7369,7499,7521
 
SQL> 
SQL> select id, max(name)
  2    from (select b.id,
  3                 wm_concat(a.ename) over(partition by id order by instr(b.name, a.empno)) name
  4            from emp a, t_con b
  5           where instr(b.name, a.empno) > 0)
  6   group by id;
 
        ID MAX(NAME)
---------- --------------------------------------------------------------------------------
         1 SMITH,ALLEN,WARD
 
SQL> 

Answer 6

直接用instrwith a as
(
  select '7369,7499,7521,7566' id from dual
)
select t.empno,t.ename from scott.emp t,a k where instr(k.id,t.empno)>0

Answer 7

这样写的缺点就是如果某个人的empno出现多次,那么得出的结果会按第一次出现取,忽略其他的出现.

Answer 8

--给楼主一个方法，用函数实现：Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> --初始数据：
SQL> select * from a;NEXT_ID
------------------------------
100,101,103,104
102,103,104SQL> select * from employee;EMPLOYEE_ID  EMPLOYEE_NAME
------ ------------
100    张三
101    李四
102    王五
103    赵六
104    田七SQL> --创建函数实现A表中每个NEXT_ID对应employee表中employee_name
SQL> CREATE OR REPLACE FUNCTION get_ename(id IN VARCHAR2 )
  2  RETURN VARCHAR2 
  3  IS
  4    result VARCHAR2(1000);  --返回结果
  5    rs     VARCHAR2(1000);  --employee_name值
  6    num1   NUMBER:=0;   --截取多少位
  7    num2   NUMBER:=1;   --从哪个位开始取子串,初始从第一位开始
  8    num3   NUMBER:=0;   --根据子串中是否存在','来判断是否是最后一个
  9    v_id   VARCHAR2(1000);  --临时id,查询employee表中对应的employee_name
 10  BEGIN
 11         FOR i IN 1..Length(id) LOOP
 12            IF SubStr(id,i,1) = Chr(44) THEN
 13                num1:=i-num2;
 14                --Dbms_Output.put_line('  i='||i||'   num1='||num1||'   num2='||num2);
 15                v_id:=SubStr(id,num2,num1);
 16                --Dbms_Output.put_line('  v_id='||v_id);
 17                num2:=i+1;
 18                --Dbms_Output.put_line('  num2='||num2);
 19                
 20                SELECT employee_name INTO rs FROM employee WHERE employee_id=v_id;
 21                result:=result||rs||',' ;
 22                --Dbms_Output.put_line('  rs='||rs||'   result='||result);
 23                --Dbms_Output.put_line('  ===================');
 24                --判断是不是最后一个v_id
 25                SELECT InStr(SubStr(id,num2),',') INTO num3 FROM a WHERE next_id=id;
 26               
 27                IF num3=0 THEN
 28                   --Dbms_Output.put_line('  i='||i||'   num1='||num1||'   num2='||num2||'  id='||id||'   num3='||num3);
 29                   SELECT employee_name INTO rs FROM employee WHERE employee_id=SubStr(id,num2);
 30                   result:=result||rs||',' ;
 31                END IF;
 32              
 33            END IF ;
 34         END LOOP;
 35         RETURN RTrim(result,','); --去掉最后一个,
 36  END;
 37  /Function created.SQL> --测试：
SQL> SELECT next_id,get_ename(next_id) employee_name FROM a;NEXT_ID            EMPLOYEE_NAME
-----------------------------------------------
100,101,103,104    张三,李四,赵六,田七102,103,104        王五,赵六,田七
SQL>