Question

我知道有一堆类似的线条,我仔细看了一看许多线索,但找不到解决办法。因此,我有一个名为“论坛”的表格,它包含我的类别(num_type=0)、线索(num_type=1)和邮递(num_type=2)。有一个专栏 num_parent,它将把线索/消息与父/母分类/线索联系起来。关于我遇到的问题,我正试图将所有类别、每个类别内的总线索以及每个类别线索内的总职位都包括在内。要做到这一点,我正在尝试:

select F.*
, count(F2.num_Forum) as num_ThreadCount
, count(F3.num_Forum) as num_PostCount
, max(F3.dte_Created) as dte_LastPost
from forum F
left outer join forum F2
on F.num_Forum=F2.num_Parent and F2.num_Type=1
left outer join forum F3
on F2.num_Forum=F3.num_Parent and F3.num_Type=2
where F.num_Type=0 and F.num_Inactive=0
group by F.num_Forum
order by F.num_Sticky desc, F.dte_Created desc

dte_ LastPost 和 num_ PostCount 正在正确显示。 num_ TreadCount 并不正确。如果我将查询分解为两个分隔查询 :

select F.* 
, count(F2.num_Forum) as num_ThreadCount
from forum F
left outer join forum F2
on F.num_Forum=F2.num_Parent and F2.num_Type=1
where F.num_Type=0 and F.num_Inactive=0
group by F.num_Forum
order by F.num_Sticky desc, F.dte_Created desc

select count(F3.num_Forum) as num_PostCount
from forum F2
left outer join forum F3
on F2.num_Forum=F3.num_Parent and F3.num_Type=2
where F2.num_Type=1 and F2.num_Inactive=0
group by F2.num_Forum
order by F2.num_Sticky desc, F2.dte_Created desc

我对每个问题都得到了正确的计算。但是, 我需要将这些计算组合起来, 这样我才能知道 Num_ PostCount 与哪个类别相对应。我分别做这个区分时看到的最大区别是, 在第二个查询中, 我能够用 F2. num_ Forum 进行分组。我试图将它添加到联合查询中, 但是它没有解决我的问题。有人知道我需要做些什么来修正我的第一个查询吗?

Answer 1

Ok,我找到了连接三个表格的查询方法,没有在职位表中设置一栏,直接连接到该类别。相反,类别链接到与职位、上下链条相连的线索。

select C.*
, count(TP.num_Thread) as num_ThreadCount
, coalesce(sum(TP.num_PostCount), 0) as num_PostCount
from forum_categories C 
left join 
(select count(P.num_Post) as num_PostCount, 
T.num_Thread, T.num_CParent, T.num_Inactive
from forum_threads T
left outer join forum_posts P
on P.num_TParent=T.num_Thread and P.num_Inactive=0
where T.num_Inactive=0
group by T.num_Thread) TP 
on C.num_Category=TP.num_CParent and TP.num_Inactive=0
where C.num_Inactive=0
group by C.num_Category
order by C.num_Sticky desc, C.dte_Created desc

友情链接