日期:2014-05-18  浏览次数:20617 次

在线求助,如何在分页查询里面,使用递归查询??
如何在分页查询里面,使用递归查询出部门dept_id=1和它的下属部门、下下属部门.

SQL code

create table userinfo(name varchar(50),dept_id int);
go
insert into userinfo values('a',1);
insert into userinfo values('b',2);
insert into userinfo values('c',3);
insert into userinfo values('d',4);
insert into userinfo values('e',5);
insert into userinfo values('f',6);
insert into userinfo values('g',7);
insert into userinfo values('h',8);
insert into userinfo values('i',9);
insert into userinfo values('j',10);
go
create table dept(dept_id int,p_dept_id int,dept_name varchar(10));
go
insert into dept values(1,0,'aa');
insert into dept values(2,0,'bb');
insert into dept values(3,1,'cc');
insert into dept values(4,1,'dd');
insert into dept values(5,2,'ee');
insert into dept values(6,3,'ff');
insert into dept values(7,4,'gg');
go
select * from (
select uml.NAME as '姓名',uml.dept_id as '部门',
Row_Number() over(order by name asc) RowNumber from userinfo uml )temp1
 where RowNumber BETWEEN  1 and  5 --分页查询
go
with my1 as(select * from dept where dept_id = 1
 union all select dept.* from my1, dept where my1.dept_id = dept.p_dept_id
)select * from my1 ;--递归查询
go

truncate table userinfo;
truncate table dept;
drop table userinfo;
drop table dept;


------解决方案--------------------
你想显示成什么样的结果啊
------解决方案--------------------
SQL code
select *
from
select uml.NAME as '姓名',uml.dept_id as '部门',Row_Number() over(order by name asc) Rowumber 
from(
(select * from dept where dept_id = 1
 union all select dept.* from my1, dept where my1.dept_id = dept.p_dept_id
)K
)L
where Rowumber between 1 and 5

不过既然使用cte,那就多分几个步骤写,不用写到一个里面,比较难理解
go

------解决方案--------------------
最外层的from少一个(
------解决方案--------------------
你不都写出来了吗?
把你的CTE写成子查询就可以了

------解决方案--------------------
SQL code
with my1 as(select * from dept where dept_id = 1
 union all select dept.* from my1, dept where my1.dept_id = dept.p_dept_id
)
select * from (
select uml.NAME as '姓名',uml.dept_id as '部门',
Row_Number() over(order by name asc) RowNumber from userinfo uml, my1 um2 where uml.dept_id=um2.dept_id)temp1
 where RowNumber BETWEEN  1 and  5
----------
a    1    1
c    3    2
d    4    3
f    6    4
g    7    5

------解决方案--------------------
探讨

SQL code
with my1 as(select * from dept where dept_id = 1
union all select dept.* from my1, dept where my1.dept_id = dept.p_dept_id
)
select * from (
select uml.NAME as '姓名',uml.dept_id as '部门',
Ro……