22,209
社区成员
发帖
与我相关
我的任务
分享关于分类树的取法
Sql2008R2
Create table #tmp_ccxh
(
ccxh varchar(20) not null,
ccnm varchar(60) not null,
cxbh varchar(10) not null
)
go
Create table #tmp_ccgn
(
cxbh varchar(10) not null
)
go
insert into #tmp_ccxh
(ccxh,ccnm,cxbh)
select '01','一级科目',''
union
select '0101','一级科目A',''
union
select '010101','一级科目A维护','A001'
union
select '0101012','一级科目A读取','A002'
union
select '0101013','一级科目A删除','A003'
union
select '0101014','一级科目A查询','A004'
union
select '0102','一级科目B',''
union
select '010201','一级科目B维护','A005'
union
select '010202','一级科目B读取','A006'
union
select '010203','一级科目B删除','A007'
union
select '010204','一级科目B查询','A008'
union
select '02','二级科目',''
union
select '0201','二级科目A',''
union
select '020101','二级科目A维护','A201'
union
select '0201012','二级科目A读取','A202'
union
select '0201013','二级科目A删除','A203'
union
select '0201014','二级科目A查询','A204'
union
select '0202','二级科目B',''
union
select '020201','二级科目B维护','A205'
union
select '020202','二级科目B读取','A206'
union
select '020203','二级科目B删除','A207'
union
select '020204','二级科目B查询','A208'
union
select '03','三级科目',''
union
select '0301','三级科目A维护','B001'
union
select '0302','三级科目A查询','B002'
union
select '0303','三级科目A作废','B003'
union
select '04','四级科目',''
union
select '0401','四级科目A维护','B003'
union
select '0402','四级科目A查询','B004'
union
select '05','五级科目维护','C001'
union
select '06','六级科目维护','D001'
insert into #tmp_ccgn
select 'A001'
UNION
select 'A005'
UNION
select 'A006'
UNION
select 'A205'
UNION
select 'A208'
UNION
select 'B002'
UNION
select 'B003'
UNION
select 'C001'
UNION
select 'D001'
select * from #tmp_ccxh
select * from #tmp_ccgn
DROP TABLE #tmp_ccxh,#tmp_ccgn
/*
--上面两个表 两个表关联后希望能将每个具体科目功能的所有上级分类都取出来
--想要获取这样的结果集
01 一级科目
0101 一级科目A
010101 一级科目A维护 A001
0102 一级科目B
010201 一级科目B维护 A005
010202 一级科目B读取 A006
02 二级科目
0202 二级科目B
020201 二级科目B维护 A205
020204 二级科目B查询 A208
03 三级科目
0302 三级科目A查询 B002
0303 三级科目A作废 B003
04 四级科目
0401 四级科目A维护 B003
05 五级科目维护 C001
06 六级科目维护 D001
*/
...全文
请发表友善的回复…
发表回复
二月十六 2018-05-28
- 打赏
- 举报
SELECT DISTINCT #tmp_ccxh.*
FROM #tmp_ccxh
JOIN
(
SELECT #tmp_ccxh.*
FROM #tmp_ccxh
JOIN #tmp_ccgn
ON #tmp_ccgn.cxbh = #tmp_ccxh.cxbh
) t
ON t.ccxh LIKE #tmp_ccxh.ccxh + '%';二月十六 2018-05-28
- 打赏
- 举报
文盲老顾 2018-05-28
- 打赏
- 举报
Create table #tmp_ccxh
(
ccxh varchar(20) not null,
ccnm varchar(60) not null,
cxbh varchar(10) not null
)
go
Create table #tmp_ccgn
(
cxbh varchar(10) not null
)
go
insert into #tmp_ccxh
(ccxh,ccnm,cxbh)
select '01','一级科目',''
union
select '0101','一级科目A',''
union
select '010101','一级科目A维护','A001'
union
select '0101012','一级科目A读取','A002'
union
select '0101013','一级科目A删除','A003'
union
select '0101014','一级科目A查询','A004'
union
select '0102','一级科目B',''
union
select '010201','一级科目B维护','A005'
union
select '010202','一级科目B读取','A006'
union
select '010203','一级科目B删除','A007'
union
select '010204','一级科目B查询','A008'
union
select '02','二级科目',''
union
select '0201','二级科目A',''
union
select '020101','二级科目A维护','A201'
union
select '0201012','二级科目A读取','A202'
union
select '0201013','二级科目A删除','A203'
union
select '0201014','二级科目A查询','A204'
union
select '0202','二级科目B',''
union
select '020201','二级科目B维护','A205'
union
select '020202','二级科目B读取','A206'
union
select '020203','二级科目B删除','A207'
union
select '020204','二级科目B查询','A208'
union
select '03','三级科目',''
union
select '0301','三级科目A维护','B001'
union
select '0302','三级科目A查询','B002'
union
select '0303','三级科目A作废','B003'
union
select '04','四级科目',''
union
select '0401','四级科目A维护','B003'
union
select '0402','四级科目A查询','B004'
union
select '05','五级科目维护','C001'
union
select '06','六级科目维护','D001'
insert into #tmp_ccgn
select 'A001'
UNION
select 'A005'
UNION
select 'A006'
UNION
select 'A205'
UNION
select 'A208'
UNION
select 'B002'
UNION
select 'B003'
UNION
select 'C001'
UNION
select 'D001'
--select * from #tmp_ccxh
--select * from #tmp_ccgn
;with t as (
select b.* from #tmp_ccgn a left join #tmp_ccxh b on a.cxbh=b.cxbh
union all
select a.* from #tmp_ccxh a,t b where a.ccxh=substring(b.ccxh,1,len(b.ccxh)-2)
)
select distinct * from t
order by 1
DROP TABLE #tmp_ccxh,#tmp_ccgn
