sql 难题 - MS-SQL Server / BI

id name manager
  1 ddd null
  2 xxx 1
  3 fff 1
  4 ggg 2
  5 hhh 2


  manger 是上级部门的ID 查出叫 hhh 的所有上级资料` 
用存储过程来实现 结果如下

  id name manager
  1 ddd null
  2 xxx 1

 

---

SQL code:
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-19 13:32:24
-- 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)
--------------------------------------------------------------------------
--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[name] NVARCHAR(10),[manager] NVARCHAR(10))
INSERT [tb]
SELECT 1,'ddd',NULL UNION ALL
SELECT 2,'xxx','1' UNION ALL
SELECT 3,'fff','1' UNION ALL
SELECT 4,'ggg','2' UNION ALL
SELECT 5,'hhh','2'
GO
--SELECT * from [tb]

-->SQL查询如下:
;with t as
(
select * from tb where name='hhh'
union all
select a.* from tb a join t b on a.id=b.manager
)
select * f