CREATE TABLE dep
(
    depID INT,
    depName VARCHAR(50),
    depParentID INT
)
INSERT INTO dep(depID,depName,depParentID) 
SELECT 1,'IT部',0 UNION ALL 
SELECT 10,'开发部',1 UNION ALL 
SELECT 11,'研发部',1 UNION ALL 
SELECT 20,'开发一组',10 UNION ALL 
SELECT 21,'开发二组',10 UNION ALL 
SELECT 22,'研发一组',11
GO

CREATE TABLE employeeInfo
(
    empID INT,
    empName VARCHAR(50),
    depID INT
)
INSERT INTO employeeInfo(empID,empName,depID)
SELECT 1,'小一',1 UNION ALL 
SELECT 2,'小二',10 UNION ALL 
SELECT 3,'小三',10 UNION ALL 
SELECT 4,'小四',11 UNION ALL 
SELECT 5,'小五',11 UNION ALL 
SELECT 6,'小六',20 UNION ALL 
SELECT 7,'小七',20 UNION ALL 
SELECT 8,'小八',21
GO

--这是我做的一部分，后面不知该怎么做
;WITH t 
AS
(
    SELECT depID,depName,depParentID FROM dep WHERE depID=1
    UNION ALL 
    SELECT p.depID,p.depName,p.depParentID FROM dep p INNER JOIN t ON t.depID=p.depParentID
),
 emp
AS
(
    SELECT depID,COUNT(1) eNum FROM employeeInfo GROUP BY depID
)
SELECT t.depID,t.depName,t.depParentID,emp.eNum FROM t 
LEFT JOIN emp ON emp.depID=t.depID 
/*
depID       depName                  depParentID eNum
----------- ------------------------ ----------- -----------
1           IT部                     0           1
10          开发部                   1           2
11          研发部                   1           2
22          研发一组                 11          NULL
20          开发一组                 10          2
21          开发二组                 10          1
*/
--下面不知该怎么做了
/*
想要的结果
eNum:总数(上级人数=下面一级的人数和+上级人数)
strLink:就是把该部门的所有员工列出来
depID       depName        depParentID eNum        strLink 
----------- -------------- ----------- ----------- ------------------------------------------------------
1           IT部           0           8           <a href='empDetail.aspx?empID=1'>小一</a>
10          开发部         1           5           <a href='empDetail.aspx?empID=2'>小二</a><a href='empDetail.aspx?empID=3'>小三</a>
11          研发部         1           2           <a href='empDetail.aspx?empID=4'>小四</a><a href='empDetail.aspx?empID=5'>小五</a>
22          研发一组       11          NULL
20          开发一组       10          2           <a href='empDetail.aspx?empID=6'>小六</a><a href='empDetail.aspx?empID=7'>小七</a>
21          开发二组       10          1           <a href='empDetail.aspx?empID=8'>小八</a>
*/

create function f_empinfo(@depid int)
returns varchar(200)
as
begin
  declare @r varchar(200)
  select 
  @r=isnull(@r,'')+'<a href=''empDetail.aspx?empID='+ltrim(empid)+'''>'+empName+'</a>'
  from employeeInfo
  where depid=@depid
  return @r
end
go


--这是我做的一部分，后面不知该怎么做
;WITH t 
AS
(
    SELECT depID,depName,depParentID FROM dep WHERE depID=1
    UNION ALL 
    SELECT p.depID,p.depName,p.depParentID FROM dep p INNER JOIN t ON t.depID=p.depParentID
),
 emp
AS
(
    SELECT depID,COUNT(1) eNum FROM employeeInfo GROUP BY depID
)
SELECT t.depID,t.depName,t.depParentID,emp.eNum,dbo.f_empinfo(t.depid) FROM t 
LEFT JOIN emp ON emp.depID=t.depID

------解决方案--------------------
create table #tmp(depID int, ulist varchar(1000))

declare @ul varchar(100)
declare @mid_o int
declare @mid_i varchar(100)
declare @esql varchar(1000)

declare cur_out cursor for

select distinct d.depID
from dep d
	inner join employeeInfo e
		on d.depID = e.depID

open cur_out
fetch next from cur_out into @mid_o
while @@fetch_status = 0
begin
	set @ul = ''
	declare cur_in cursor for

	select e.empName
	from dep d
		inner join employeeInfo e
			on d.depID = e.depID
	where d.depID = @mid_o
	open cur_in