部门表
depid name depid_up
001 部门一 003
002 部门二 003
003 大部门一 null
004 部门三 003
005 大部门二 null
006 大部门三 null
007 部门四 005
008 部门五 005
....
....
----------------
如何得到这样的结构:
003 大部门一
001 --部门一--
002 --部门二--
004 --部门三--
005 大部门二
007 --部门四--
008 --部门五--
006 大部门三
...
---------------
用来填充DropDownList的!!!
谢谢啦!!!
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/
--楼上方法可行,不过如果仅仅只有两层。大可不必这样。
create table t4(id int,diqu varchar(20),sign int)
insert into t4 select 1 , '黑龙江省', 0
insert into t4 select 2 , '河北省', 0
insert into t4 select 3 , '吉林省' , 0
insert into t4 select 4 , '牡丹江市' , 1
insert into t4 select 5 , '哈尔滨市' , 1
insert into t4 select 6 , '保定市' , 2
insert into t4 select 7 , '长春市' , 3
insert into t4 select 8 , '河南省' , 0
insert into t4 select 9 , '郑州市' , 8select id,diqu from
(
select id , diqu, px=1 from t4 where sign = 0
union all
select a.id,'---'+b.diqu, px=2 from t4 a,t4 b where a.id = b.sign
) t
order by id, px
create table 部门表(depid varchar(20),name varchar(20),depid_up varchar(20))
insert into 部门表 select '001','部门一','003'
union all select '002','部门二','003'
union all select '003','大部门一',''
union all select '004','部门三','003'
union all select '005','大部门二',''
union all select '006','大部门三',''
union all select '007','部门四','005'
union all select '008','部门五','005'
select depid,name from
(
select depid , name, px=1 from 部门表 where isnull(depid_up,'')=''
union all
select a.depid,'--'+b.name+'--', px=2 from 部门表 a,部门表 b where a.depid=b.depid_up
) t
order by depid, px
insert into tb values('001', '部门一' , '003')
insert into tb values('002', '部门二' , '003')
insert into tb values('003', '大部门一' , null )
insert into tb values('004', '部门三' , '003')
insert into tb values('005', '大部门二' , null )
insert into tb values('006', '大部门三' , null )
insert into tb values('007', '部门四' , '005')
insert into tb values('008', '部门五' , '005')
goselect * , px = identity(int,1,1) into tmp from
(
select * from tb where depid_up is null
union all
select depid_up depid , name , depid depid_up from tb where depid_up is not null
) t
order by depid , depid_upselect depid , name from
(
select depid , name , px from tmp where depid_up is null
union all
select depid_up depid , name , px from tmp where depid_up is not null
) t
order by pxdrop table tb , tmp/*
depid name
---------- ----------
003 大部门一
001 部门一
002 部门二
004 部门三
005 大部门二
007 部门四
008 部门五
006 大部门三
(所影响的行数为 8 行)
*/
insert into 部门表 select '001','部门一','003'
union all select '002','部门二','003'
union all select '003','大部门一',''
union all select '004','部门三','003'
union all select '005','大部门二',''
union all select '006','大部门三',''
union all select '007','部门四','005'
union all select '008','部门五','005'
select depid,name from
(
select depid , name, px=1 from 部门表 where isnull(depid_up,'')=''
union all
select a.depid,'--'+b.name+'--', px=2 from 部门表 a,部门表 b where a.depid=b.depid_up
) t
order by depid, px
(
id int identity(1,1) not null,
name char(10)
)
insert dep select '部门一'
insert dep select '部门二'
insert dep select '大部门一'
insert dep select '部门三'
insert dep select '大部门二'
insert dep select '大部门三'
insert dep select '部门四'
insert dep select '部门五'
select * from dep(@name char(20))alter function pro_dep(@name char(20))
returns char(20)
as
begin
declare @str char(10)
set @str=''
select @str='--'+name+'--' from dep where name=@name
return @str
end
select id,dbo.pro_dep(name)as name from dep where substring(name,1,1)<>'大'
union
select id,name from dep where substring(name,1,1)='大'
order by name
select id,'--'+name+'--' from dep where substring(name,1,1)<>'大'
union
select id,name from dep where substring(name,1,1)='大'这样也行,更简单