求SQL语句

[chenzhengsi]

表结构
文档表:
{文档ID，所属栏目ID}

栏目表:
{栏目ID，父栏目ID}

求SQL统计结果：列出所有一级栏目名和一级栏目下(本栏目中文档数+所有二级栏目中文档数+所有三级栏目中文档数)之和,形如：
--------------
栏目ID,文档数
栏目1，23
栏目2,0
栏目3,44
--------------
其中"文档数=(本栏目中文档数+所有二级栏目中文档数+所有三级栏目中文档数)"

不使用存储过程或变量
非常感谢！

[chenzhengsi]

能否解释一下
(
select distinct a.id as AiD ,A.id AS iid from DO a inner join DO b on a.id=b.fid inner join DO c on b.id=c.fid WHERE a.fid IS NULL
UNION
select distinct a.id as AiD,b.id AS iid from DO a inner join DO b on a.id=b.fid inner join DO c on b.id=c.fid WHERE a.fid IS NULL
UNION
select distinct a.id as AiD,c.id AS iid from DO a inner join DO b on a.id=b.fid inner join DO c on b.id=c.fid WHERE a.fid IS NULL)
表示的意思？

[chenzhengsi]

lwl0606(寒泉) :
我应用到实际环境中的时候，只取出了前两个栏目的统计数值，后面还有6个栏目没有数据
能否加你QQ？我的是21536980

[hillhx]

select a.lmid,count(distinct wdid) as wd_count
from t_lm a left join
t_lm b on a.lmid = b.flmid left join
t_lm c on b.lmid = c.flmid inner join
t_wd d on a.lmid = d.lmid or b.lmid=d.lmid or c.lmid=d.lmid
where a.lm_level = 1
group by a.lmid

[hillhx]

我就是给你个思路啊，你自己在琢磨琢磨

[akuzou]

不使用存储过程或变量?
----

楼主的要求估计办不到,应该要写一个函数

[lwl0606]

这是文档表:DID:文档ID，ID:所属栏目ID

create table d (DID INT ,id INT )
INSERT INTO D
SELECT 1,1
UNION SELECT 2,1
UNION SELECT 3,2
UNION SELECT 4,2
UNION SELECT 5,5
UNION SELECT 6,6
UNION SELECT 7,7
UNION SELECT 8,11
UNION SELECT 9,11
UNION SELECT 10,12
UNION SELECT 11,13
UNION SELECT 12,14
UNION SELECT 13,12
UNION SELECT 14,15
UNION SELECT 15,18
UNION SELECT 16,12
UNION SELECT 17,18

这是栏目表 ID ：栏目ID，FID :父栏目ID

create table DO (ID INT ,FID INT)
INSERT INTO DO
SELECT 1 ,NULL
UNION SELECT 2 ,1
UNION SELECT 3 ,2
UNION SELECT 4 ,3
UNION SELECT 5 ,2
UNION SELECT 6 ,2
UNION SELECT 7 ,2
UNION SELECT 8 ,3
UNION SELECT 9 ,6
UNION SELECT 10 ,9
UNION SELECT 11 ,NULL
UNION SELECT 12 ,11
UNION SELECT 13 ,11
UNION SELECT 14 ,12
UNION SELECT 15 ,13
UNION SELECT 16 ,15
UNION SELECT 17 ,15
UNION SELECT 18 ,13
UNION SELECT 19 ,17


select a.aid,Count(distinct did) as Total
from (
select distinct a.id as AiD ,A.id AS iid from DO a inner join DO b on a.id=b.fid inner join DO c on b.id=c.fid WHERE a.fid IS NULL
UNION
select distinct a.id as AiD,b.id AS iid from DO a inner join DO b on a.id=b.fid inner join DO c on b.id=c.fid WHERE a.fid IS NULL
UNION
select distinct a.id as AiD,c.id AS iid from DO a inner join DO b on a.id=b.fid inner join DO c on b.id=c.fid WHERE a.fid IS NULL)
a inner join D on a.iid=d.id
group by a.aid

--结果
iID Total
1 7
11 10

[chenzhengsi]

非常感谢！！！！