怎样得到一级中的所有的下一级(包括下一级的下一级.级数不确定!!) (在线等!)
CREATE TABLE [dbo].[AGENT] (
[agentid] [int] NOT NULL ,
[superior] [int] NULL ,--上一级编号
[linkman] [int] NULL ,
[account] [int] NULL
)
在Hibernate中写HQL语句.
要求是:查询这一级的下一级的下一级的下一级.....,0代表最上级.
实在不行:就
请给个类似的自己调自己的便利方法!
谢谢!!!
[解决办法]
这个是例子.楼主参考一下
drop table t_1
go
create table t_1(code varchar(10),parent varchar(10))
insert into t_1(code,parent)
select 'A ', '0 '
union all select 'E ', 'A '
union all select 'E01 ', 'E '
union all select 'E02 ', 'E '
go
create table t_2(code varchar(10),value int)
insert into t_2(code,value)
select 'E01 ',3
union all select 'E02 ',4
go
--递归取下级
create function f_getchild(@code varchar(10))
returns @t table(code varchar(10))
as
begin
declare @t_temp table(id int identity(1,1),child varchar(10))
insert into @t(code)
select code from t_1 where Parent = @code
insert into @t_temp(child)
select code from t_1 where Parent = @code
declare @child_temp varchar(10),@max_id int,@min_id int
select @max_id = max(id),@min_id = min(id) from @t_temp
while @min_id <= @max_id
begin
select @child_temp = child from @t_temp where id = @min_id
insert into @t(code)
select * from dbo.f_getchild(@child_temp)
select @min_id = @min_id + 1
end
return
end
--调用
select code from dbo.f_getchild( 'E ')
code
----------
E01
E02
（所影响的行数为 2 行）
[解决办法]
create table customer(id int,name varchar(20),email varchar(20))
create table dianying(id int,name varchar(20))
create table zhongjian(id int ,cid int ,did int)
insert into customer values(3, 'ccccc ', 'eeeee@23.ondo ')
insert into dianying values(2, '积极 ')
insert into zhongjian values(2,1,3)
select * from customer
select * from dianying
select * from zhongjian
select c.name,c.email,d.name from customer as c,dianying as d where c.id=2 and d.id=(select did from zhongjian where cid=2)
