读书人
栏目▼

求1SQL语句100分

发布时间： 2012-09-07 10:38:15 作者： rapoo

求一SQL语句100分

SQL code
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>*/


[解决办法]
SQL code
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
[解决办法]
SQL code
--计算数量;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*/
[解决办法]
探讨

嫂子，您的语句可以给我讲解下吗？就是emp中和t中的语句不太理解
读书人网 >SQL Server

热点推荐

触屏版 | 电脑版

读书人网 m.reader8.net