Oracle数据库语句

xb_a_zuo

已于 2022-09-28 16:09:43 修改

阅读量598

点赞数

分类专栏： Oracle学习笔记文章标签： oracle

于 2022-01-05 16:48:00 首次发布

本文链接：https://blog.csdn.net/m0_51159082/article/details/122327077

版权

Oracle学习笔记专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.连接符为||

select * from 表 where 字段值 like '%'||字段||'%'

2.多行转成一行以逗号分割

select workno, wmsys.wm_concat(name) 
from table group by workno;

3.列转行

SELECT DocumentID,substr(a.AuthDept, instr(a.AuthDept, ',', 1, levels.lvl) + 1, instr(a.AuthDept, ',', 1, levels.lvl + 1) -(instr(a.AuthDept, ',', 1, levels.lvl) + 1)) as AuthDept,Chargeuser
FROM
(SELECT DocumentID,',' || to_char(AuthDept) || ',' AS AuthDept,(length(to_char(AuthDept) || ',') - nvl(length(REPLACE(to_char(AuthDept), ',')), 0))/1 AS cnt,to_char(Chargeuser) as Chargeuser
FROM T_ChapAuth) a,
(SELECT rownum AS lvl
FROM (SELECT (MAX(length(to_char(AuthDept) || ',') - nvl(length(REPLACE(to_char(AuthDept), ',')), 0)))/1 max_len FROM T_ChapAuth)
CONNECT BY LEVEL <= max_len) levels
WHERE levels.lvl <= a.cnt
ORDER BY DocumentID,levels.lvl