求1SQL语句100分

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,'研发一组',11GOCREATE 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,'小八',21GO--这是我做的一部分，后面不知该怎么做;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), empAS(    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           110          开发部                   1           211          研发部                   1           222          研发一组                 11          NULL20          开发一组                 10          221          开发二组                 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          NULL20          开发一组       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)asbegin  declare @r varchar(200)  select   @r=isnull(@r,'')+'<a href=''empDetail.aspx?empID='+ltrim(empid)+'''>'+empName+'</a>'  from employeeInfo  where [email protected]  return @rendgo--这是我做的一部分，后面不知该怎么做;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), empAS(    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