listagg 反函数

drop table str;
create table str(seq number,str varchar2(100)) ;
insert into str values(1,'AA,BB,CC,DD;AA1,BB1,CC,DD1;AA2,BB2,CC,DD2;AA3,BB3,CC3,DD3;');
insert into str values(2,'AA,BB,CC1,DD;AA1,BB1,CC,DD1;AA2,BB2,CC,DD2;AA3,BB3,CC,DD3;');
insert into str values(3,'AA,BB,CC,DD;AA1,CC,BB,DD1;AA2,BB2,CC,DD2;AA3,BB3,CC3,DD3;');
COMMIT;
--1
SELECT seq,
         rtrim(regexp_substr(str || ';', '.*?' || ';', 1, LEVEL), ';') AS str
    FROM str
  CONNECT BY seq = PRIOR seq
         AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
         AND LEVEL <= regexp_count(str, ';')
--2
select distinct seq, regexp_substr(str, '[^;]+', 1, level) str
  from str
connect by level <= regexp_count(str, ';')
 order by seq

我是没看懂 有看懂的评论解释下

下面是一个mysql数据库的sql 解决以父数据为核心  连接 子数据及 爷数据 也急 上中下游数据连接

select * from (

SELECT a.aid, a.aname,  a.bid, a.bname, b.bid cid,b.banme cname,a.amount_a,a.amount_b FROM
(
SELECT
@rn:= CASE WHEN @securityid = aid THEN @rn + 1 ELSE 1 END AS rownum,
@securityid:= aid as tempId,
 a.*
FROM
(SELECT a.id aid,a.name aname,a.amount_a,a.amount_b,b.id bid,b.name bname from ent a left join ent b ON a.id = b.rel_id  	WHERE a.type = 0  AND b.type = 1 ORDER BY a.id) a
,(SELECT @rn=0, @securityid=0) b
)a
  left join
(
SELECT
@rn:= CASE WHEN @securityid = aid THEN @rn + 1 ELSE 1 END AS rownum,
@securityid:= aid as tempId,
 a.*
FROM
(SELECT a.id aid,a.name aname,b.id bid,b.name banme from ent a left join ent b ON a.id = b.rel_id 	WHERE a.type = 0 AND b.type = 2    ORDER BY a.id) a
,(SELECT @rn=0, @securityid=0) b
)b on a.aid = b.aid and a.rownum = b.rownum

union

SELECT b.aid, b.aname,  a.bid, a.bname, b.bid cid,b.banme cname,b.amount_a,b.amount_b FROM
(
SELECT
@rn:= CASE WHEN @securityid = aid THEN @rn + 1 ELSE 1 END AS rownum,
@securityid:= aid as tempId,
 a.*
FROM
(SELECT a.id aid,a.name aname,b.id bid,b.name bname from ent a left join ent b ON a.id = b.rel_id  	WHERE a.type = 0  AND b.type = 1 ORDER BY a.id) a
,(SELECT @rn=0, @securityid=0) b
)a
  right join
(
SELECT
@rn:= CASE WHEN @securityid = aid THEN @rn + 1 ELSE 1 END AS rownum,
@securityid:= aid as tempId,
 a.*
FROM
(SELECT a.id aid,a.name aname,a.amount_a,a.amount_b,b.id bid,b.name banme from ent a left join ent b ON a.id = b.rel_id 	WHERE a.type = 0 AND b.type = 2    ORDER BY a.id) a
,(SELECT @rn=0, @securityid=0) b
)b on a.aid = b.aid and a.rownum = b.rownum

) a

又是一个mysql

用于 ''1,2,3'' 字段的拆分   为   1 行  2行  3 行

SELECT
          
            t.crt_user_name,
            check_user_name,
            t.crt_time,
            t.flow_step
        FROM
            (
                SELECT  a.*,
                        substring_index( substring_index( a.repo_org, ',', b.help_topic_id + 1 ), ',',- 1 ) orgId
                FROM
                    nb_ybj_task AS a
                JOIN mysql.help_topic AS b
                ON b.help_topic_id  <(char_length( repo_org )- char_length( REPLACE ( repo_org, ',', ''))+ 1 )
                where id  = #{param1.taskId}
            ) a
        LEFT JOIN task_org t ON a.orgId = t.org_id
                                      	AND t.task_id = a.id
                                        AND t.MONTH = #{param1.month}
     
        order by  flow_step desc,crt_time desc