SQL2000中 如何取一个类别下的所有子类,不只是取,而且要按树型显示
解决方案 »
- sql2000 行级,能加排他锁吗?
- 保存字符串时,如果字符串中只有一个撇号,就会报错,如何解决呀?[如何做到容错处理]
- 很简单的问题但是可能没人能解决!绝不是挑衅不信来试试!
- 请问如何打开一个数据库的日志文件.....................???
- sql server、access数据库,max函数能用来求字符字段的最大值吗?
- 请问sql server2000的端口
- 有SQL SERVER2000关安装问题
- 有关begin transaction的问题
- 在一个sql语句中能否嵌套调用一个存储过程呀?
- 数据库被逻辑删除的字段避居然影响程序的运行???呜.....出手帮忙哪
- 高分求一存储过程
- 亲人们啊,帮忙解决一下吧,急啊.
insert into @t values('001', 'A' , null)
insert into @t values('002', 'B' , '001')
insert into @t values('003', 'C' , '001')
insert into @t values('004', 'D' , null)
insert into @t values('005', 'E' , '002')
insert into @t values('006', 'F' , '004')
insert into @t values('007', 'G' , '003')
declare @restlt table(id varchar(10),Name varchar(10), parentId varchar(10),path varchar(1000))insert into @restlt
select id,Name,parentId,id from @t where parentId is null or parentId not in (select id from @t )while exists(select * from @t as a,@restlt as b where a.parentId=b.id and a.id not in (select id from @restlt)
)
insert into @restlt
select a.*, b.path+'/'+a.id from @t as a,@restlt as b where a.parentId=b.id and a.id not in (select id from @restlt)select * from @restlt order by path,id
001 A NULL 001
002 B 001 001/002
005 E 002 001/002/005
003 C 001 001/003
007 G 003 001/003/007
004 D NULL 004
006 F 004 004/006
sql 2005 用 withUSE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, EmployeeLevel
FROM DirectReports ;
GO
. 使用递归公用表表达式显示层次列表
以下示例在示例 C 的基础上添加经理和雇员的名称,以及他们各自的头衔。通过缩进各个级别,突出显示经理和雇员的层次结构。 复制代码
USE AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
1,
CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.ManagerID IS NULL
UNION ALL
SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
c.FirstName + ' ' + c.LastName),
e.Title,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
LastName)
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports
ORDER BY Sort;
GO