--A表 create table ta as select 1 as id, '大类A' as name, null as parentid from dual union all select 2, '小类A', 1 from dual union all select 3 , '大类B', null from dual union all select 4 ,'小类B', 3 from dual union all select 5 ,'小类B1', 3 from dual--B表create table tb as select 100 as id, '商品A' as name, 2 as classid from dual union all select 101 ,'商品B', 2 from dual union all select 102 ,'商品C', 4 from dual union all select 103 ,'商品D', 5 from dualselect t.id,t.name,t.parentid from ( select b.id,b.name,b.classid as parentid,b.classid as sid from tb b union all select a.id,a.name,a.parentid,a.id as sid from ta a where exists(select 1 from tb where a.id=tb.classid) ) t order by sid,parentid/* 想要得到的效果: id name parentid 2 小类A 1 100 商品A 2 101 商品B 2 4 小类B 3 102 商品C 4 5 小类B1 3 103 商品D 5 */
create table ta as select
1 as id, '大类A' as name, null as parentid from dual
union all select
2, '小类A', 1 from dual
union all select
3 , '大类B', null from dual
union all select
4 ,'小类B', 3 from dual
union all select
5 ,'小类B1', 3 from dual--B表create table tb as select
100 as id, '商品A' as name, 2 as classid from dual
union all select
101 ,'商品B', 2 from dual
union all select
102 ,'商品C', 4 from dual
union all select
103 ,'商品D', 5 from dualselect t.id,t.name,t.parentid from (
select b.id,b.name,b.classid as parentid,b.classid as sid from tb b
union all
select a.id,a.name,a.parentid,a.id as sid from ta a where exists(select 1 from tb where a.id=tb.classid) ) t
order by sid,parentid/*
想要得到的效果:
id name parentid
2 小类A 1
100 商品A 2
101 商品B 2
4 小类B 3
102 商品C 4
5 小类B1 3
103 商品D 5
*/