一般数据库在存储时都会选择存代码值,但是在运维中经常会用到通过SQL将这些值转化成明文显示,就查询语句进行分享,办法比较笨拙,仅供参考。
使用场景需求说明
业务表S_A
ID | NAME | PRODUCT_TYPE |
1 | 测试1 | 04 |
2 | 测试2 | 02,03 |
3 | 测试3 | 01,03,05 |
代码表DM_B
ID | CODE | NAME |
1 | 01 | 论文 |
2 | 02 | 著作 |
3 | 03 | 报告 |
4 | 04 | 专利 |
5 | 05 | 其他 |
需要查询的结果
ID | NAME | PRODUCT_TYPE | PRODUCT_NAME |
1 | 测试1 | 04 | 专利 |
2 | 测试2 | 02,03 | 著作,报告 |
3 | 测试3 | 01,03,05 | 论文,报告,其他 |
1、创建一个视图对LEVEL进行转换
CREATE OR REPLACE VIEW VIEW_A AS
SELECT
ID, --需要查询表中的的字段,唯一主键
NAME,--需要查询表中的的字段,如名称
regexp_substr(PRODUCT_TYPE, '[^,]+', 1, level) AS PRODUCT_TYPE --需要拆分的字段,直接把PRODUCT_TYPE换成你要拆分的那个字段即可
from
S_A -- 表名
where
1=1 --你如果有条件,在此处加上即可,没有的话去掉加1=1
connect by level <= regexp_count(PRODUCT_TYPE, ',') + 1 --需要拆分的字段,直接把PRODUCT_TYPE换成你要拆分的那个字段即可,如果有多个字段需要同时拆分,用and复制level语句即可
and ID = prior ID --需要查询表中的的字段,唯一主键,把ID换成你表中的唯一主键即可,其他不变
and prior dbms_random.value is not null --这句话照抄,不要改
视图结果如下
2、通过上面的视图进行查询
SELECT
ID,
NAME,
PRODUCT_TYPE,
(SELECT LISTAGG((select NAME from DM_B WHERE CODE = a.PRODUCT_TYPE),',') within GROUP (order by a.ID) from VIEW_A a where a.ID=S_A.id GROUP BY a.ID) AS PRODUCT_NAME
from S_A;
得到最终结果如下,基本满足想要的结果。
这个不是最优方案,只是解决查询问题,如果有更好的方案欢迎指教。