Select name as 子部门 from TableName where code like '01%'
select name 子部门 from 表 where left(code,2)='01'
DECLARE @code int SELECT * FROM #t WHERE supercode=like 'LEFT(code,Len(code)-2)%' AND code=@code)
select name as 子部门 from TableName where supercode=(select code from TableName where name='大部门')
create table test ( code varchar(10), name varchar(20), supercode varchar(10) )insert into testselect '01','大部门',' ' union select '0101','二级部门','01' union select '010101','三级部门','0101' union select '0102','二级部门','01' union select '010102','三级部门','0101' union select '010103','三级部门','0101'create proc usp_text @dept varchar(10)as set nocount on declare @s varchar(8000) set @s='' set @s ='select name as 子部门 from test where left(supercode,len('+''''+@dept+''''+'))'+'='+@dept exec (@s) returnexec usp_text '0101'子部门 -------------------- 三级部门 三级部门 二级部门drop table test drop proc usp_text
exec usp_text '0101' 这里的'0101'更改为你需要查找的!
select * from tbl where pid not in (select id from tbl); ---所有根节点 select * from tbl where id not in (select pid from tbl); ---所有叶子节点 select * from tbl where id in (select pid from tbl); ---所有父节点
再帮帮忙 regithanhu 的方法是对的 我想再加个条件 or code='0101' 想要的结果是 010101,010102
select name from '表名' where supercode=(select name from '表名')
select name 子部门 from 表 where left(code,2)='01'
SELECT * FROM #t WHERE supercode=like 'LEFT(code,Len(code)-2)%' AND code=@code)
(
code varchar(10),
name varchar(20),
supercode varchar(10)
)insert into testselect '01','大部门',' '
union
select '0101','二级部门','01'
union
select '010101','三级部门','0101'
union
select '0102','二级部门','01'
union
select '010102','三级部门','0101'
union
select '010103','三级部门','0101'create proc usp_text
@dept varchar(10)as
set nocount on declare @s varchar(8000)
set @s=''
set @s ='select name as 子部门 from test
where left(supercode,len('+''''+@dept+''''+'))'+'='+@dept exec (@s)
returnexec usp_text '0101'子部门
--------------------
三级部门
三级部门
二级部门drop table test
drop proc usp_text
这里的'0101'更改为你需要查找的!
select * from tbl where id not in (select pid from tbl); ---所有叶子节点
select * from tbl where id in (select pid from tbl); ---所有父节点
想要的结果是 010101,010102