Oracle数据库中某个表中的某个字段的值是用逗号隔开的多个值，根据逗号拆分并从关联表中查出数据返回

最新推荐文章于 2024-09-24 09:12:50 发布

原创最新推荐文章于 2024-09-24 09:12:50 发布 · 3.1k 阅读

7 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

7 篇文章

订阅专栏

本文介绍在Oracle数据库中，如何处理一个字段内存储多个编码值的问题，这些编码值可能由英文逗号、中文逗号或空格分隔。通过重写FIND_IN_SET函数和使用wm_concat函数，实现从编码表中查询对应的编码名称。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

工作中遇到一个问题，表A中有一个字段，存的是编码值，但是不规范，存的多个值，用逗号（既有英文逗号，又有中文逗号，还有空格）隔开了；现需要根据编码值关联对应的编码表查出编码名称，所以网上查找资料得出了解决方法。本人用的是Oracle数据库。

参考MySQL数据库解决方法：https://blog.youkuaiyun.com/myme95/article/details/82661661

一、创建测试数据表

1.bi_test表（course_id既有英文逗号，又有中文逗号，还有空格）

2.bm_test表

3.希望得到的结果：

(1)

(2)

二、使用的函数

1.MySQL数据库可以直接使用FIND_IN_SET函数和group_concat()函数实现。详情戳https://blog.youkuaiyun.com/myme95/article/details/82661661 查看MySQL实现方法。

2.Oracle重写FIND_IN_SET函数（参考自：https://www.cnblogs.com/Iqiaoxun/p/5606639.html）

CREATE OR REPLACE
FUNCTION FIND_IN_SET(piv_str1 varchar2, piv_str2 varchar2, p_sep varchar2 := ',',p_sep2 varchar2 := '，')
RETURN varchar2 IS
/*
该函数用于判断 piv_str1 是否在 piv_str2 中，p_sep 截取内容为英文逗号，p_sep2 截取内容为中文逗号
如果是，作为条件，返回对应数据，如果piv_str1 不在piv_str2 或piv_str2 为空字符串，则返回值为 000999 。
*/
  l_idx    number:=0; -- 用于计算piv_str2中分隔符的位置
  str      varchar2(500); -- 根据分隔符截取的子字符串
  piv_str varchar2(500) := replace(piv_str2,' ',''); -- 将piv_str2赋值给piv_str ，并去掉空格
  res varchar2(500):='000999'; -- 返回结果
  loopIndex number:=0;
BEGIN
-- 如果piv_str中没有分割符，直接判断piv_str1和piv_str是否相等，相等返回对应数据
IF instr(piv_str, p_sep, 1) = 0 and instr(piv_str, p_sep2, 1) = 0 THEN
   IF piv_str = piv_str1 THEN
      res:= piv_str1;
   END IF;
ELSE
-- 循环按p_sep截取piv_str
LOOP
    l_idx := instr(piv_str,p_sep);
     loopIndex:=loopIndex+1;
-- 当piv_str中还有分隔符时
      IF l_idx > 0 THEN

   -- 截取第一个分隔符前的字段str
         str:= substr(piv_str,1,l_idx-1);
   -- 判断 str 和piv_str1 是否相等，相等返回对应数据并结束循环判断
         IF str = piv_str1 THEN
           --res:= loopIndex;
           res:= piv_str1;
           EXIT;
         END IF;
        piv_str := substr(piv_str,l_idx+length(p_sep));
      ELSE
   -- 当截取后的piv_str 中不存在分割符时，判断piv_str和piv_str1是否相等，相等返回对应数据
        IF piv_str = piv_str1 THEN
           --res:= loopIndex;
           res:= piv_str1;
        END IF;
        -- 无论最后是否相等，都跳出循环
        EXIT;
      END IF;
END LOOP;
-- 结束循环

-- 循环按p_sep2截取piv_str
LOOP
    l_idx := instr(piv_str,p_sep2);
     loopIndex:=loopIndex+1;
-- 当piv_str中还有分隔符时
      IF l_idx > 0 THEN

   -- 截取第一个分隔符前的字段str
         str:= substr(piv_str,1,l_idx-1);
   -- 判断 str 和piv_str1 是否相等，相等返回对应数据并结束循环判断
         IF str = piv_str1 THEN
           --res:= loopIndex;
           res:= piv_str1;
           EXIT;
         END IF;
        piv_str := substr(piv_str,l_idx+length(p_sep2));
      ELSE
   -- 当截取后的piv_str 中不存在分割符时，判断piv_str和piv_str1是否相等，相等返回对应数据
        IF piv_str = piv_str1 THEN
           --res:= loopIndex;
           res:= piv_str1;
        END IF;
        -- 无论最后是否相等，都跳出循环
        EXIT;
      END IF;
END LOOP;
-- 结束循环
END IF;
-- 返回res
RETURN res;
END FIND_IN_SET;

3.Oracle使用wm_concat函数代替mysql中的 group_concat()函数

三、实现的SQL语句

(1)

SELECT * FROM(

SELECT a.*,FIND_IN_SET( b.course_id, a.course_id)cid FROM bi_test a,bm_test b)m,bm_test n

WHERE m.cid = n.course_id

ORDER BY m.student_id

结果：

（2）

select a.student_id ,a.name,a.course_id ,wm_concat(b.course_name)
from bi_test a JOIN bm_test b
on FIND_IN_SET( b.course_id, a.course_id) = b.course_id
GROUP BY a.name,a.course_id,a.student_id
ORDER BY a.student_id

结果：