表名称table
字段1 code
字段2 子code
字段3 名称
code的子code的名称,然后是子code的子code的名称...一直到子code没有为止
例
code 子code 名称
0001 0002 dddd
0001 0003 ffff
0002 0004 gggg
0003 0005 ssss
0004 0010 jjjjj
0005 0006 rrrrr
0008 0009 wwww 需要的查询结果是
层号 code 名称
1 0001 dddd
2 0002 gggg
3 0004 gggg
4 0010 jjjj
2 0003 ssss
3 0005 rrrr
4 0006 无
尽量不要用存储过程,能否用sql语句完成呢
名称是code的名称
然后根据层号莱分层的阿
显示在tree上的
字段1 code
字段2 子code
字段3 名称
code的子code的名称,然后是子code的子code的名称...一直到子code没有为止
例
code 子code 名称
0001 0002 dddd
0001 0003 ffff
0002 0004 gggg
0003 0005 ssss
0004 0010 jjjjj
0005 0006 rrrrr
0008 0009 wwww 需要的查询结果是
层号 code 名称
1 0001 dddd
2 0002 gggg
3 0004 gggg
4 0010 jjjj
2 0003 ssss
3 0005 rrrr
4 0006 无
尽量不要用存储过程,能否用sql语句完成呢
名称是code的名称
然后根据层号莱分层的阿
显示在tree上的
字段1 code 字段1 code
字段2 子code 字段2 名称
code的子code的名称,然后是子code的子code的名称...一直到子code没有为止 上面的表的情况修改一下
--刚写了一个类似的:
create table bommd(md001 varchar(10),md003 varchar(10))
insert into bommd select 'H','A'
insert into bommd select 'A','B'
insert into bommd select 'A','C'
insert into bommd select 'B','D'
insert into bommd select 'B','E'
insert into bommd select 'C','F'
insert into bommd select 'C','G'go
DECLARE @T table(md001 varchar(10),md003 varchar(10),lev int,[Order] varchar(8000))
DECLARE @lev int
SET @Lev=0
INSERT @T SELECT md001,md003,@Lev,md001+md003
FROM bommd t
WHERE not exists(select 1 from bommd where md003=t.md001)
WHILE @@ROWCOUNT>0
BEGIN
SET @Lev=@Lev+1
INSERT @T SELECT a.md001,a.md003,@Lev,b.[Order]+a.md003
FROM bommd a,@T b
WHERE a.md001=b.md003
AND b.Lev=@Lev-1
ENDSELECT a.md001,a.md003
FROM bommd a,@T b
WHERE a.md003=b.md003
group by b.[Order],a.md001,a.md003
ORDER BY b.[Order]
CREATE TABLE T
(
ID VARCHAR(20),
PID VARCHAR(20),
MC VARCHAR(30)
)
INSERT INTO T
SELECT '0001','0002','dddd' UNION ALL
SELECT '0001','0003','ffff' UNION ALL
SELECT '0002','0004','gggg' UNION ALL
SELECT '0003','0005','ssss' UNION ALL
SELECT '0004','0010','jjjjj' UNION ALL
SELECT '0005','0006','rrrrr' UNION ALL
SELECT '0008','0009','wwww'
GO
CREATE FUNCTION F_SORT()
RETURNS @t TABLE( T_ID INT IDENTITY(1,1), ID VARCHAR(20), PID VARCHAR(20), MC VARCHAR(30), FLAG VARCHAR(20),LVL INT)
AS
BEGIN
DECLARE @i INT
SET @i = 1
INSERT INTO @t (ID,PID,MC,LVL)
SELECT A.*,@i FROM t A WHERE NOT EXISTS ( SELECT * FROM T WHERE PID = A.ID)
AND EXISTS ( SELECT * FROM T WHERE ID = A.PID)
UPDATE A SET FLAG = (SELECT RIGHT(10000+COUNT(1),2) FROM @t WHERE T_ID<A.T_ID)
FROM @t A
WHILE EXISTS ( SELECT * FROM @t A,t B WHERE A.PID = B.ID AND A.LVL = @i)
BEGIN
INSERT INTO @t
SELECT B.*,A.FLAG,@i+1 FROM @t A,t B WHERE A.PID = B.ID AND A.LVL = @i
UPDATE A SET FLAG = FLAG + (SELECT RIGHT(100000+COUNT(1),2) FROM @t WHERE T_ID<A.T_ID)
FROM @t A WHERE LVL = @i+1
SET @i = @i + 1
END
INSERT INTO @t
SELECT B.PID,NULL,NULL,A.FLAG,@i+1 FROM @t A,t B WHERE A.PID = B.ID AND A.LVL = @i-1
UPDATE A SET FLAG = FLAG + (SELECT RIGHT(100000+COUNT(1),2) FROM @t WHERE T_ID<A.T_ID)
FROM @t A WHERE LVL = @i +1 DELETE FROM @t WHERE T_ID IN
(SELECT T_ID FROM @t A WHERE EXISTS ( SELECT * FROM @t B WHERE A.ID = B.ID AND A.T_ID > B.T_ID)) RETURN
END
GO SELECT ID,MC FROM DBO.F_SORT() ORDER BY FLAG
DROP FUNCTION F_SORT
DROP TABLE TID MC
-------------------- ------------------------------
0001 dddd
0002 gggg
0004 jjjjj
0010 NULL
0003 ssss
0005 rrrrr
0006 NULL(所影响的行数为 7 行)
SELECT LVL,ID,MC FROM DBO.F_SORT() ORDER BY FLAGLVL ID MC
----------- -------------------- ------------------------------
1 0001 dddd
2 0002 gggg
3 0004 jjjjj
4 0010 NULL
2 0003 ssss
3 0005 rrrrr
4 0006 NULL(所影响的行数为 7 行)
go
CREATE TABLE T
(
ID VARCHAR(20),-----转一下位置
child VARCHAR(20),
MC VARCHAR(30)
)INSERT INTO T
SELECT '0001','0002','dddd' UNION ALL
SELECT '0001','0003','ffff' UNION ALL
SELECT '0002','0004','gggg' UNION ALL
SELECT '0003','0005','ssss' UNION ALL
SELECT '0004','0010','jjjjj' UNION ALL
SELECT '0005','0006','rrrrr' UNION ALL
SELECT '0008','0009','wwww'
go
--drop table t
DECLARE @T table(ID varchar(10),child varchar(10),MC VARCHAR(30),lev int,[Order] nvarchar(200))
DECLARE @lev int
SET @Lev=2
INSERT @T SELECT ID,child,MC,@Lev, ID+child
FROM T t1
WHERE not exists(select 1 from T where child=t1.ID)WHILE @@ROWCOUNT>0
BEGIN
SET @Lev=@Lev+1
INSERT @T
SELECT a.ID,a.child,a.MC,@Lev,b.[Order]+a.child
FROM T a,@T b
WHERE a.ID=b.child
AND b.Lev=@Lev-1
ENDselect
[Lev]=1,[ID],[MC]=min(MC),[order]=[ID]
from
@t t
where
not exists(select 1 from @t where child=t.ID)
group by [ID]union all select
a.Lev,a.child,isnull(b.MC,'无'),a.[order]
from
@t a
left join
@t b on a.child=b.ID
order by
a.[order]
(所影响的行数为 3 行)
(所影响的行数为 2 行)
(所影响的行数为 2 行)
(所影响的行数为 0 行)Lev ID MC order
----------- ---------- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 0001 dddd 0001
2 0002 gggg 00010002
3 0004 jjjjj 000100020004
4 0010 无 0001000200040010
2 0003 ssss 00010003
3 0005 rrrrr 000100030005
4 0006 无 0001000300050006
1 0008 wwww 0008
2 0009 无 00080009(所影响的行数为 9 行)
go
CREATE TABLE T
(
ID VARCHAR(20),-----转一下位置
child VARCHAR(20),
MC VARCHAR(30)
)INSERT INTO T
SELECT '0001','0002','dddd' UNION ALL
SELECT '0001','0003','ffff' UNION ALL
SELECT '0002','0004','gggg' UNION ALL
SELECT '0003','0005','ssss' UNION ALL
SELECT '0004','0010','jjjjj' UNION ALL
SELECT '0005','0006','rrrrr' UNION ALL
SELECT '0008','0009','wwww'
go
--drop table t
DECLARE @T table(ID varchar(10),child varchar(10),MC VARCHAR(30),lev int,[Order] nvarchar(200))
DECLARE @lev int
SET @Lev=2
INSERT @T SELECT ID,child,MC,@Lev, ID+child
FROM T t1
WHERE not exists(select 1 from T where child=t1.ID)WHILE @@ROWCOUNT>0
BEGIN
SET @Lev=@Lev+1
INSERT @T
SELECT a.ID,a.child,a.MC,@Lev,b.[Order]+a.child
FROM T a,@T b
WHERE a.ID=b.child
AND b.Lev=@Lev-1
ENDselect
[Lev],[ID],[MC]
from
(select
[Lev]=1,[ID],[MC]=min(MC),[order]=[ID]
from
@t t
where
not exists(select 1 from @t where child=t.ID)
group by [ID]union all select
a.Lev,a.child,isnull(b.MC,'无'),a.[order]
from
@t a
left join
@t b on a.child=b.ID
)t
order by
[order]------不显示排序规则列
(所影响的行数为 3 行)
(所影响的行数为 2 行)
(所影响的行数为 2 行)
(所影响的行数为 0 行)Lev ID MC
----------- ---------- ------------------------------
1 0001 dddd
2 0002 gggg
3 0004 jjjjj
4 0010 无
2 0003 ssss
3 0005 rrrrr
4 0006 无
1 0008 wwww
2 0009 无(所影响的行数为 9 行)
(
ID VARCHAR(20),
PID VARCHAR(20),
MC VARCHAR(30)
)
INSERT INTO T
SELECT '0001','0002','dddd' UNION ALL
SELECT '0001','0003','ffff' UNION ALL
SELECT '0002','0004','gggg' UNION ALL
SELECT '0003','0005','ssss' UNION ALL
SELECT '0004','0010','jjjjj' UNION ALL
SELECT '0005','0006','rrrrr' UNION ALL
SELECT '0008','0009','wwww'
GOCREATE FUNCTION getLVL
(@id VARCHAR(20),@lvl INT)
RETURNS INT
ASBEGIN
DECLARE @pid VARCHAR(20)
myLoop:
BEGIN
SET @pid=@id
SELECT @id=pid,@lvl=@lvl+1
FROM
(
SELECT id FROM T
UNION
SELECT pid FROM T
) a
LEFT JOIN T b
ON a.id=b.id
WHERE a.id=@pid
END
IF @id IS NOT NULL
GOTO myLoop
RETURN @lvl
END
GO
SELECT *,dbo.getLVL(id,0)
FROM
(
SELECT id FROM T
UNION
SELECT pid FROM T
) xDROP TABLE T
DROP FUNCTION getLVL