如果表中有部门id,上级部门id,部门名称三个字段,如何得到按照上下级排序的查询结果
解决方案 »
- 会员教育经历的存储与搜索
- **********************请教一个关于查询的问题**************************
- Convert
- 谢谢 LGXYZ 兄弟的帮忙
- 谁知道在安装好的SQL server 2005上怎么来设置sa口令
- 再問一個 :iif( instr([Param]![Com],[svr_Mast]![Com]>0,yes,No)=yes如何轉化為SQL
- 设计数据库时字段怎么起名?
- 以下SQL语句怎么写啊?急啊!!!!!!!!!!(数据库教科书的习题)
- SQL server打开后出的问题,请帮忙!谢了!
- 帮忙看一下这个存储过程中的动态SQL语句那里有问题?
- 关于SQL分割的问题,请高人指点?
- 如何在UNIX中配置Microsoft SQL Server 2000 Driver for JDBC?[
---------------------------------------------------------------------------- Author : htl258(Tony)-- Date : 2010-04-23 02:37:28-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)-- Subject: BOM按节点排序应用实例-------------------------------------------------------------------------- --实例1:--> 生成测试数据表:tb IF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb]GOCREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))INSERT [tb]SELECT 1,'01',0,N'服装' UNION ALLSELECT 2,'01',1,N'男装' UNION ALLSELECT 3,'01',2,N'西装' UNION ALLSELECT 4,'01',3,N'全毛' UNION ALLSELECT 5,'02',3,N'化纤' UNION ALLSELECT 6,'02',2,N'休闲装' UNION ALLSELECT 7,'02',1,N'女装' UNION ALLSELECT 8,'01',7,N'套装' UNION ALLSELECT 9,'02',7,N'职业装' UNION ALLSELECT 10,'03',7,N'休闲装' UNION ALLSELECT 11,'04',7,N'西装' UNION ALLSELECT 12,'01',11,N'全毛' UNION ALLSELECT 13,'02',11,N'化纤' UNION ALLSELECT 14,'05',7,N'休闲装'GO--SELECT * FROM [tb] -->SQL查询如下: ;WITH T AS( SELECT CAST(CODE AS VARCHAR(20)) AS CODE,*, CAST(ID AS VARBINARY(MAX)) AS px FROM tb AS A WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.pid) UNION ALL SELECT CAST(B.CODE+A.CODE AS VARCHAR(20)),A.*, CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX)) FROM tb AS A JOIN T AS B ON A.pid=B.id)SELECT Code,Name FROM T ORDER BY px/*Code Name-------------------- ----------01 服装0101 男装010101 西装01010101 全毛01010102 化纤010102 休闲装0102 女装010201 套装010202 职业装010203 休闲装010204 西装01020401 全毛01020402 化纤010205 休闲装 (14 行受影响)*/ --实例2:--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10))
INSERT [tb]
SELECT 1,0,'test1' UNION ALL
SELECT 2,0,'test2' UNION ALL
SELECT 3,1,'test1.1' UNION ALL
SELECT 4,2,'test2.1' UNION ALL
SELECT 5,3,'test1.1.1' UNION ALL
SELECT 6,1,'test1.2'
GO
--SELECT * FROM [tb]-->SQL查询如下:
;WITH T AS
(
SELECT *,CAST(ID AS VARBINARY(MAX)) AS px
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[parentid])
UNION ALL
SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))
FROM tb AS A
JOIN T AS B
ON A.[parentid]=B.id
)
SELECT [id],[parentid],[categoryname] FROM T
ORDER BY px
/*
id parentid categoryname
----------- ----------- ------------
1 0 test1
3 1 test1.1
5 3 test1.1.1
6 1 test1.2
2 0 test2
4 2 test2.1(6 行受影响)
*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2010/04/23/5518166.aspx
/*
标题:SQL SERVER 2000中树查询显示层次
作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间:2010-02-4
地点:新疆乌鲁木齐
*/create table tb(id int , pid int , name varchar(10))
insert into tb values(1 , 0 , '广东省')
insert into tb values(2 , 1 , '广州市')
insert into tb values(3 , 1 , '深圳市')
insert into tb values(4 , 2 , '天河区')
insert into tb values(5 , 3 , '罗湖区')
insert into tb values(6 , 3 , '福田区')
insert into tb values(7 , 3 , '宝安区')
insert into tb values(8 , 7 , '西乡镇')
insert into tb values(9 , 7 , '龙华镇')
insert into tb values(10 ,7 , '松岗镇')
gocreate function f_getnum(@id int) returns varchar(4000)
as
begin
declare @ret varchar(4000) , @pid int
set @ret = right('0000' + rtrim(@id) , 4)
while exists(select 1 from tb where id = @id and pid <> 0 )
begin
select @pid = pid from tb where id = @id and pid <> 0
set @id = @pid
set @ret = right('0000' + rtrim(@id) , 4) + @ret
end
return @ret
end
goselect id , name , REPLICATE('-' , len(dbo.f_getnum(id))/4 - 1) + name as name from tb order by dbo.f_getnum(id)drop function f_getNum
drop table tb/*
id name name
----------- ---------- ---------------
1 广东省 广东省
2 广州市 -广州市
4 天河区 --天河区
3 深圳市 -深圳市
5 罗湖区 --罗湖区
6 福田区 --福田区
7 宝安区 --宝安区
8 西乡镇 ---西乡镇
9 龙华镇 ---龙华镇
10 松岗镇 ---松岗镇(所影响的行数为 10 行)*/
create table tb (id int , Name varchar(10) , pid int )
insert into tb values(1 ,'广东省' , 0)
insert into tb values(2 ,'四川省' , 0)
insert into tb values(3 ,'湖北省' , 0)
insert into tb values(4 ,'东莞市' , 1)
insert into tb values(5 ,'广州市' , 1)
insert into tb values(6 ,'天河区' , 5)
insert into tb values(7 ,'绵阳市' , 2)
insert into tb values(8 ,'武汉市' , 3)
insert into tb values(9 ,'汉口区' , 8)
insert into tb values(10,'随州市' , 3)
gocreate function f_getnum(@id int) returns varchar(4000)
as
begin
declare @ret varchar(4000) , @pid int
set @ret = right(' ' + rtrim(@id) , 4)
while exists(select 1 from tb where id = @id and pid <> 0 )
begin
select @pid = pid from tb where id = @id and pid <> 0
set @id = @pid
set @ret = right(' ' + rtrim(@id) , 4) + @ret
end
return @ret
end
goselect id , name , REPLICATE(' ' , len(dbo.f_getnum(id))/4 - 1) + name as name from tb order by dbo.f_getnum(id)drop function f_getNum
drop table tb/*
id name name
----------- ---------- --------------
1 广东省 广东省
4 东莞市 东莞市
5 广州市 广州市
6 天河区 天河区
2 四川省 四川省
7 绵阳市 绵阳市
3 湖北省 湖北省
8 武汉市 武汉市
9 汉口区 汉口区
10 随州市 随州市(所影响的行数为 10 行)
*/