create table class(id int,parent_id int,classname varchar(10),islast bit) insert class select 1,0,'类一',0 union all select 2,1,'类二',0 union all select 3,1,'类三',1 union all select 4,2,'类四',0 union all select 5,4,'类五',1 union all select 6,1,'类六',1create table info(id int,classid int,name varchar(10)) insert info select 1,3,'X' union all select 2,5,'T' union all select 3,4,'Y' union all select 4,6,'Z' union all select 5,3,'C' union all select 6,5,'T' go--查询处理的函数 create function f_cid(@id int) returns @re table(id int,level int,islast bit,parent_id int) as begin declare @l int set @l=0 insert @re select id,@l,islast,parent_id from class where id=@id while @@rowcount>0 begin set @l=@l+1 insert @re select a.id,@l,a.islast, case when @l=1 then a.id when @l>1 then b.parent_id else a.parent_id end from class a,@re b where a.parent_id=b.id and b.level=@l-1 end delete from @re where islast=0 return end go--调用函数实现查询(查询基础1) select top 5 a.*,b.parent_id from info a,f_cid(1) b where a.classid=b.id go--删除测试 drop table class,info drop function f_cid/*--结果id classid name parent_id ----------- ----------- ---------- ----------- 1 3 X 3 2 5 T 2 4 6 Z 6 5 3 C 3 6 5 T 2(所影响的行数为 5 行) --*/
我要按parent_id分类取每个parent_id前6个怎么取啊?我想用group by b.parent_id,可是不行
怎么按parent_id法? 不明白.
id classid name parent_id ----------- ----------- ---------- ----------- 1 3 X 3 2 5 T 2 4 6 Z 6 5 3 C 3 6 5 T 2 这样的结果是取前5个,我想的是按照他们的parent_id每个id取1个。结果这样的id classid name parent_id ----------- ----------- ---------- ----------- 4 6 Z 6 5 3 C 3 6 5 T 2
自己改出来了select b.parent_id,a.* from info a, (select a.*,b.parent_id from info a,f_cid(1) b where a.classid=b.id) b , (select id from class where parent_id =1) c where a.id = b.id and a.id in (select top 1 id from (select a.*,b.parent_id from info a,f_cid(1) b where a.classid=b.id) b where b.parent_id=c.id )
create table class(id int,parent_id int,classname varchar(10),islast bit)
insert class select 1,0,'类一',0
union all select 2,1,'类二',0
union all select 3,1,'类三',1
union all select 4,2,'类四',0
union all select 5,4,'类五',1
union all select 6,1,'类六',1create table info(id int,classid int,name varchar(10))
insert info select 1,3,'X'
union all select 2,5,'T'
union all select 3,4,'Y'
union all select 4,6,'Z'
union all select 5,3,'C'
union all select 6,5,'T'
go--查询处理的函数
create function f_cid(@id int)
returns @re table(id int,level int,islast bit,parent_id int)
as
begin
declare @l int
set @l=0
insert @re select id,@l,islast,parent_id from class where id=@id
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,@l,a.islast,
case
when @l=1 then a.id
when @l>1 then b.parent_id
else a.parent_id end
from class a,@re b
where a.parent_id=b.id and b.level=@l-1
end
delete from @re where islast=0
return
end
go--调用函数实现查询(查询基础1)
select top 5 a.*,b.parent_id
from info a,f_cid(1) b
where a.classid=b.id
go--删除测试
drop table class,info
drop function f_cid/*--结果id classid name parent_id
----------- ----------- ---------- -----------
1 3 X 3
2 5 T 2
4 6 Z 6
5 3 C 3
6 5 T 2(所影响的行数为 5 行)
--*/
----------- ----------- ---------- -----------
1 3 X 3
2 5 T 2
4 6 Z 6
5 3 C 3
6 5 T 2
这样的结果是取前5个,我想的是按照他们的parent_id每个id取1个。结果这样的id classid name parent_id
----------- ----------- ---------- -----------
4 6 Z 6
5 3 C 3
6 5 T 2
(select a.*,b.parent_id from info a,f_cid(1) b where a.classid=b.id) b ,
(select id from class where parent_id =1) c
where a.id = b.id
and a.id in (select top 1 id from (select a.*,b.parent_id from info a,f_cid(1) b where a.classid=b.id) b where b.parent_id=c.id )