很有技术含量的一个ＳＱＬ，大家来试试

Question

CREATE TABLE [dbo].Department(
[DEPT_ID] [decimal](19, 0) NOT NULL,　/**主键**/
[FULL_DEPT_ID] [varchar](300) NULL,　　/**fullid,用于快速检索下级数据**/
[F_FULL_DEPT_NAME] [varchar](600) NULL,
        [F_PARENT_DEPT_ID] [decimal](19, 0),  /**上级部门id**/
[F_NODE_LEVEL] [decimal](10, 0) NULL, /**结点层次**/
[F_DEPT_TYPE] [decimal](19, 0)  /**结点类型**/
)INSERT INTO [dbo].[Department]
([DEPT_ID]
   , [FULL_DEPT_ID]
   , [F_FULL_DEPT_NAME]
   , [F_NODE_LEVEL]
   , [F_DEPT_TYPE])
VALUES
(1
, '1'
, '中达通信'
, 0
, 0)
  , (1020
, '1/1020'
, '中达通信/海外市场'
, 1
, NULL)
  , (41051
, '1/1020/41051'
, '中达通信/海外市场/总项目部'
, 2
, 2),
(41052
, '1/1020/41051/41052'
, '中达通信/海外市场/总项目部/子项目部'
, 3
, 2),
(1050
, '1/1050'
, '中达通信/国内市场'
, 1
, 1),
(42051
, '1/1050/42051'
, '中达通信/国内市场/项目部'
, 2
, 2),
(1080
, '1/1080'
, '中达通信/其它区域'
, 1
, 2)

  GO我的目的是根据一个以逗号分隔的id列表(如：41052，42051,1080)，获取到该部门对应的所属业务部门，所属业务部门的获取算法如下：
从本层开始往上层找，找到第一个部门类型[F_DEPT_TYPE]为0或1的部门
如 select .....where DEPT_ID in (41052，42051,1080)
得的的结果应该是：
id         所属业务部门
41052      中达通信/海外市场
42051      中达通信/国内市场
1080       中达通信

Answer 1

其实有个很方便的地方就是我有一个fullid,这个字段是将所有本上层id用/号分隔成的一个值，
如果能用
SELECT * from T_ORG_DEPT D1
JOIN 
T_ORG_DEPT D2
ON 1=1
where 
D2.F_DEPT_LEVEL_TYPE_ID in (0,1)
AND CONVERT(VARCHAR,D2.F_DEPT_ID) in(REPLACE (D1.F_FULL_DEPT_ID,'/',',')) 
 order by d1.F_FULL_DEPT_ID desc
这样来取就很方便了，但是由于REPLACE字段后的值sql会当做一个字符串，所以这样做会有问题

Answer 2

;with t as (
select dept_id,
'/'+convert(nvarchar(max),dept_id)+'/' as DEPT ,
'/'+convert(nvarchar(max),full_dept_id)+'/' as [ROOT],
f_full_dept_name,
F_dept_type,
row_number()over(order by f_full_dept_name) as rowid,
1 as seq
 from [Department] where dept_id in (42051,41052,1080)
union all
select 
t.dept_id,
'/'+convert(nvarchar(max),a.dept_id)+t.DEPT,
t.ROOT,
a.f_full_dept_name,
a.F_dept_type,
t.rowid,
t.seq+1
 from [Department] a inner join t
on CHARINDEX('/'+convert(nvarchar(max),a.dept_id)+t.DEPT,t.[ROOT])>0 
)
select * from t
where exists (select 1 from (
select min(temp.seq) as seq,temp.rowid from t temp
where isnull (temp.F_dept_type,0) in (1,0)
group by temp.rowid)temp1
where temp1.rowid=t.rowid
and temp1.seq=t.seq)

Answer 3

hierarchyid类型可以使问题简单化
http://www.cnblogs.com/chenxizhang/archive/2009/04/26/1444016.html

Answer 4

谢谢版主的回复，但为了考虑到不同数据库的兼容性，暂时不能使用这么前卫的功能，还是用朴素算法实现的好，duoxu1983　同学给的语句我看了一下好像是可以，但我还没看太明白，正在研究

Answer 5

F_PARENT_DEPT_ID 这个字段为什么没用上？

Answer 6

CTE中就是一个简单的递归。rowid 是为了区分你输入的多个变量。
seq是用来确定每一级的深度（用于你之后确认“第一个部门类型[F_DEPT_TYPE]为0或1的部门”）。 

Answer 7

呵呵 刚去搜索了一下CTE 之前都没听说过程学习了
另外弱弱的问一句 假如我这张表有几万条数据库这个语句会不会很慢呢？

Answer 8

duoxu1983 
还想请教一下，这个递归的次数是由哪一句来决定的，是那个WHERE条件吗？