我正在尝试使用递归查询来获取每个类别(包括子类别)的事件数。我有 3 个表 - 内容选项卡(分层表)、事件和中间表 RelEvents到内容选项卡,所以它是简单的多对多关系。问题是当我使用下面的查询时,我得到了每个类别的事件数,但没有子类别的事件数。我正在使用 SQL Server 2008。有什么想法吗?
WITH ContentTabsStructure (Id, Name)
AS
(
SELECT Id, Name,parentId FROM ContentTabs
WHERE Id =1
UNION ALL
SELECT ct.Id, ct.Name,ct.parentId FROM ContentTabs AS ct
INNER JOIN ContentTabsStructure AS cts
ON ct.ParentId = cts.Id
)
SELECT cts.id,cts.Name, Count(distinct e.id) as NumberOfEvents
FROM ContentTabsStructure cts
INNER JOIN RelEventsToContentTabs etct
ON cts.id = etct.contentTabId
INNER JOIN Events e
ON etct.eventId = e.id
GROUP BY cts.id,cts.Name
您也可以在 CTE 中包含 parentId,如下所示:
WITH ContentTabsStructure (Id, Name,parentId)
获取每个类别的子类别,然后在所选列中包含如下所示的内容,以获取子类别的事件数:
, NumberOfSubCatagoryEvents = isnull(
(
Count(distinct e.id)
FROM ContentTabsStructure cts1
INNER JOIN RelEventsToContentTabs etct1
ON cts1.id = etct1.contentTabId
INNER JOIN Events e1
ON etct1.eventId = e1.id
where cts1.parentId<cts.parentId
GROUP BY cts1.id,cts1.Name
), 0 )