求一SQL语句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 depid=@depid 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
fetch next from cur_in into @mid_i
while @@fetch_status = 0
begin
if(@ul <> '')
begin
set @ul = @ul + ',' + @mid_i
end
if(@ul = '')
begin
set @ul = @mid_i
end
fetch next from cur_in into @mid_i
end
if(@ul<>'') set @esql = 'insert into #tmp values(' + convert(varchar(10), @mid_o) + ',''' + @ul + ''')'
exec(@esql)
close cur_in
deallocate cur_in
fetch next from cur_out into @mid_o
end
close cur_out
deallocate cur_out;
with cte(depID, depName, depParentID, empID) as (
select d.depID, d.depName, d.depParentID, e.empID
from dep d
left join employeeInfo e
on d.depID = e.depID
where d.depID not in (select distinct depParentID from dep)
union all
select d.depID, d.depName, d.depParentID, e.empID
from dep d
inner join employeeInfo e
on d.depID = e.depID
inner join cte c
on c.depParentID = d.depID
)
select c.depID, c.depName, count(c.empID) as ecnt, t.ulist
from cte c
inner join #tmp t
on c.depID = t.depID
group by c.depID, c.depName, t.ulist
order by c.depID, c.depName
drop table #tmp
[解决办法]
[code=SQL][/code]
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
fetch next from cur_in into @mid_i
while @@fetch_status = 0
begin
if(@ul <> '')
begin
set @ul = @ul + ',' + @mid_i
end
if(@ul = '')
begin
set @ul = @mid_i
end
fetch next from cur_in into @mid_i
end
if(@ul<>'') set @esql = 'insert into #tmp values(' + convert(varchar(10), @mid_o) + ',''' + @ul + ''')'
exec(@esql)
close cur_in
deallocate cur_in
fetch next from cur_out into @mid_o
end
close cur_out
deallocate cur_out;
with cte(depID, depName, depParentID, empID) as (
select d.depID, d.depName, d.depParentID, e.empID
from dep d
left join employeeInfo e
on d.depID = e.depID
where d.depID not in (select distinct depParentID from dep)
union all
select d.depID, d.depName, d.depParentID, e.empID
from dep d
inner join employeeInfo e
on d.depID = e.depID
inner join cte c
on c.depParentID = d.depID
)
select c.depID, c.depName, count(c.empID) as ecnt, t.ulist
from cte c
inner join #tmp t
on c.depID = t.depID
group by c.depID, c.depName, t.ulist
order by c.depID, c.depName
drop table #tmp
[解决办法]
不好意思...菜鸟不会回帖....借用4楼的函数...
with cte(depID, depName, depParentID, empID) as (
select d.depID, d.depName, d.depParentID, e.empID
from dep d
left join employeeInfo e
on d.depID = e.depID
where d.depID not in (select distinct depParentID from dep)
union all
select d.depID, d.depName, d.depParentID, e.empID
from dep d
inner join employeeInfo e
on d.depID = e.depID
inner join cte c
on c.depParentID = d.depID
)
select c.depID, c.depName, count(c.empID) as ecnt, dbo.f_empinfo(c.depID)
from cte c
where dbo.f_empinfo(c.depID) is not null
group by c.depID, c.depName
order by c.depID, c.depName
[解决办法]
declare @sql varchar(2000)
declare @mid varchar(10)
declare cur cursor for
select depID from dep order by depID
open cur
fetch next from cur into @mid
while @@fetch_status = 0
begin
set @sql = 'with cte(depID, depName, depParentID) as (
select d.depID, d.depName, d.depParentID
from dep d
where d.depID = ' + @mid + '
union all
select d.depID, d.depName, d.depParentID
from dep d
inner join cte c
on c.depID = d.depParentID
)
insert into result
select *, ' + @mid + ' as level
from cte'
--print(@sql)
exec(@sql)
fetch next from cur into @mid
end
close cur
deallocate cur
select r.level, count(e.empID)
from result r
left join employeeInfo e
on r.depID = e.depID
group by r.level
[解决办法]
参照一下我原来的提问，请仔细看。
http://topic.csdn.net/u/20091223/14/b6662d31-7145-45ee-8aa4-972c03870657.html
[解决办法]
--计算数量;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 t.depID,isnull(e.eNum,0) as eNum ,t.depName,t.depParentID from t left join ( SELECT depID,COUNT(1)as eNum from employeeInfo group by depID ) as e on t.depID=e.depID union all select t.depID,eNum,t.depName,t.depParentID from emp e,t where e.depParentID=t.depID)select depID,depName,depParentID,sum(eNum) as eNum from emp group by depID,depName,depParentID/*depID depName depParentID eNum----------- -------------------------------------------------- ----------- -----------1 IT部 0 810 开发部 1 511 研发部 1 220 开发一组 10 221 开发二组 10 122 研发一组 11 0*/
[解决办法]
