Try:
Select count(*) from category where category_id_f = 0
Union all
Select count(*) from category where category_id_f in (Select category_id from category where category_id_f = 0)
union all
Select count(*) from category where category_id_f in (Select category_id from category where category_id_f in (Select category_id from category where category_id_f = 0) )
Select count(*) from category where category_id_f = 0
Union all
Select count(*) from category where category_id_f in (Select category_id from category where category_id_f = 0)
union all
Select count(*) from category where category_id_f in (Select category_id from category where category_id_f in (Select category_id from category where category_id_f = 0) )
select *,(select count(*) from category where category_id_f=root.category_id or category_id_f in (select category_id from category where category_id_f=root.category_id)) 和 from category root
http://expert.csdn.net/Expert/topic/2285/2285830.xml?temp=.1570551/*--树形数据处理方案 树形数据的排序,新增,修改,复制,删除,数据完整性检查,汇总统计
--邹建 2003.9--*/
returns varchar(5000)
as
begin
declare @FolderID varchar(50),@Str varchar(5000)
set @Str = ''
declare sub_folder cursor scroll local for
select category_id from category where category_id_f = @ID
open sub_folder
fetch next from sub_folder into @FolderID
while @@fetch_status=0
begin
set @Str = @Str+@FolderID + ','
if exists(select category_id from category where category_id_f = @FolderID)
begin
if @@NESTLEVEL < 32
set @Str = @Str + dbo.sub_FolderID(@FolderID)
else
begin
select @FolderID = category_id from category where category_id_f = @FolderID
set @Str = @Str + ',' + @FolderID
end
end
fetch next from sub_folder into @FolderID
end
close sub_folder
deallocate sub_folder
return @Str
end执行:select dbo.sub_FolderID(0) from category
你现在给出的是求第二层的总数,能给出求第三层的吗?(第三层的父id是第二层的id)
(select count(*) from category where category_id_f=root.category_id or category_id_f in
(select category_id from category where category_id_f=root.category_id)
--^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^这句不是吗?
) 和 from category root
id name id_f
1 cat1 0 -->第一层
2 cat2 0 -->第一层
3 cat3 0 -->第一层
4 cat11 1 -->第二层
5 cat12 1 -->第二层
6 cat111 4 -->第三层
7 cat112 4 -->第三层
我想要的检索结果是
id name category2_sum category3_sum
1 cat1 2 2
2 cat2 0 0
3 cat3 0 0
(select count(*) from category where category_id_f in (select category_id from category where category_id_f=root.category_id)) category3_sumfrom category root where category_id_f=0