select a.fchrFunctionID ,a.fchrFunctionName,a.fchrFunctionDesc
,count(fchrTopicID) as c3,count(FchrForumID) as c4
from OUT_T_Function a FULL
OUTER JOIN OUT_T_Topic b on a.fchrFunctionID=b.fchrFunctionID
FULL OUTER JOIN OUT_T_Forum c on b.fchrTopicID =c.FchrFatherID
group by a.fchrFunctionID ,a.fchrFunctionName,a.fchrFunctionDesc第一张表5条
第二章 3条
第三张表3条我想得到板块的全部信息 主题的数目 帖子得数目
,count(fchrTopicID) as c3,count(FchrForumID) as c4
from OUT_T_Function a FULL
OUTER JOIN OUT_T_Topic b on a.fchrFunctionID=b.fchrFunctionID
FULL OUTER JOIN OUT_T_Forum c on b.fchrTopicID =c.FchrFatherID
group by a.fchrFunctionID ,a.fchrFunctionName,a.fchrFunctionDesc第一张表5条
第二章 3条
第三张表3条我想得到板块的全部信息 主题的数目 帖子得数目
-- 先试一下这个咯
SELECT aa.fchrFunctionID ,aa.fchrFunctionName,aa.fchrFunctionDesc,
bb.T_Count as 帖子总数
FROM OUT_T_Function aa,
(
SELECT fchrFunctionID,
(
SELECT COUNT(*)FROM OUT_T_Forum
WHERE FchrFatherID=a.fchrTopicID
) as T_Count
FROM OUT_T_Topic a
)bb
WHERE aa.fchrFunctionID=bb.fchrFunctionID
select a.fchrFunctionID ,a.fchrFunctionName,a.fchrFunctionDesc
,count(fchrTopicID) as c3,count(FchrForumID) as c4
from OUT_T_Function a LEFT
OUTER JOIN OUT_T_Topic b on a.fchrFunctionID=b.fchrFunctionID
LEFT OUTER JOIN OUT_T_Forum c on b.fchrTopicID =c.FchrFatherID
group by a.fchrFunctionID ,a.fchrFunctionName,a.fchrFunctionDesc
a.id=b.B_id b.id=c._id 取出a得全部 b和c得对应 数目
表a id data
1 p
2 p
3 p
4 p
表b id data B_id
1 o 3
2 o 3
3 0 3表c id data c_id
1 u 1
2 u 1
3 u 2---------->得到
1 p 0 0
2 p 0 0
3 p 3 3
4 p 0 0
select a.fchrFunctionID ,a.fchrFunctionName,a.fchrFunctionDesc
,count(fchrTopicID) as c3,count(FchrForumID) as c4
from OUT_T_Function a LEFT
OUTER JOIN OUT_T_Topic b on a.fchrFunctionID=b.fchrFunctionID
LEFT OUTER JOIN OUT_T_Forum c on b.fchrTopicID =c.FchrFatherID
where b.fchrTopicID is not null
group by a.fchrFunctionID ,a.fchrFunctionName,a.fchrFunctionDesc
left join
(select b_id , count(*) cnt from b group by b_id) m on a.id = b.b_id
left join
(select b.b_id , count(*) from b , c where b.id = c.c_id group by b_id) n on a.id = n.b_id
insert into A values(1 , 'p')
insert into A values(2 , 'p')
insert into A values(3 , 'p')
insert into A values(4 , 'p')
create table B(id int, data varchar(10) , b_id int)
insert into B values(1 , 'o', 3 )
insert into B values(2 , 'o', 3 )
insert into B values(3 , '0', 3 )
create table C(id int, data varchar(10) , c_id int)
insert into C values(1 , 'u' , 1 )
insert into C values(2 , 'u' , 1 )
insert into C values(3 , 'u' , 2 )
select a.* , isnull(m.cnt , 0) b_cnt , isnull(n.cnt , 0) c_cnt from a
left join
(select b_id , count(*) cnt from b group by b_id) m on a.id = m.b_id
left join
(select b.b_id , count(*) cnt from b , c where b.id = c.c_id group by b_id) n on a.id = n.b_iddrop table A,B,C/*
id data b_cnt c_cnt
----------- ---------- ----------- -----------
1 p 0 0
2 p 0 0
3 p 3 3
4 p 0 0(所影响的行数为 4 行)
*/
SELECT id,data,
(
SELECT COUNT(*) FROM TableB
WHERE b_id=a.id
) as b_count,
(
SELECT COUNT(*)
FROM TableB b ,TableC c
WHERE b.id=c.c_id
AND b.B_id=a.id
) as c_count
FROM TableA as a
能不能帮我改成我表啊
我都看不懂你写
谢谢了
select a.fchrFunctionID ,a.fchrFunctionName,a.fchrFunctionDesc
,count(fchrTopicID) as c3,count(FchrForumID) as c4
from OUT_T_Function a FULL
OUTER JOIN OUT_T_Topic b on a.fchrFunctionID=b.fchrFunctionID
FULL OUTER JOIN OUT_T_Forum c on b.fchrTopicID =c.FchrFatherID
group by a.fchrFunctionID ,a.fchrFunctionName,a.fchrFunctionDesc
select a.fchrFunctionID ,a.fchrFunctionName,a.fchrFunctionDesc
,count(distinct fchrTopicID) as c3,count(distinct FchrForumID) as c4
from OUT_T_Function a LEFT
OUTER JOIN OUT_T_Topic b on a.fchrFunctionID=b.fchrFunctionID
LEFT OUTER JOIN OUT_T_Forum c on b.fchrTopicID =c.FchrFatherID
group by a.fchrFunctionID ,a.fchrFunctionName,a.fchrFunctionDesc头晕,吃饭去咯。
对应出点问题
帮下啦
OUT_T_Function (表a)
fchrFunctionID (id)OUT_T_Topic (表b)
fchrTopicID (id) fchrFunctionID (B_ID对应表a的 id) OUT_T_Forum (表C)
FchrForumID (id) FchrFatherID (C_ID 对应b得id)
帮我再加个最后发表的主题(或帖子)好吗 谢谢 明天加分
表OUT_T_Topic 和OUT_T_Forum 分别有个时间字段fdtmDate