一个无限类别表，一个文章表，如何统计任何一个类别下的全部文章数

Question

类别表  app_KbCategory
C_ID                 类别ID
C_CName          类别名称
C_ParentID       上级分类ID，0:为最高级
C_ShowOrder    显示顺序
C_Level            当前分类所在层数。1：为顶层
C_ChildCount    当前分类子分类数
C_IsInclude      是否包含文章，1:是0:否
-----------------------------------------
C_ID  C_ParentID  C_CName  C_ShowOrder  C_Level C_ChildCount  C_IsInclude
1        0                 数据库       1                     1           2                    0
2        0                 网络          2                     1           1                    0
3        1                 access       1                    2            0                    0
4        1                 sql            2                     2           0                    0
5        2                 vpn           1                     2           0                    0
-----------------------------------------
类别显示：
-数据库
--access
----无限
--sql
----无限
--无限
-网络
--vpn
----无限
--无限
-无限
-----------------------------------------
文章表 app_KbDetails|
D_ID                 知识库内容ID号
D_ParentID        所属分类ID
D_Title               标题
D_Author           作者
D_Content         内容
D_Published       发布时间
D_Modified        修改时间
D_Views           浏览次数
-----------------------------------------
请问我该如何在C#写SQL语句统计任何一个类别下的全部文章数
比如在web页面显示为：
数据库（20）
--access（15）
--sql（5）
网络（5）
--vpn（5）

Answer 1

-- create 2 similiar tables
CREATE TABLE [dbo].[Article](
	[Article_ID] [int] NOT NULL,
	[Category_ID] [int] NOT NULL,
 CONSTRAINT [PK_Article] PRIMARY KEY CLUSTERED 
(
	[Article_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Category](
	[CategoryID] [int] NOT NULL,
	[CategoryName] [nvarchar](50) NOT NULL,
	[ParentCategoryID] [int] NOT NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

-- to see all the records

select * from Article join Category on Article.Category_ID=Category.CategoryID;

-- answer to your question: query the count by each category, including its sub categories 你的问题的答案

with tempTable as
(
select Article_ID, Category_ID from Article

union all

select Article_ID, c.ParentCategoryID from tempTable T join Category c on T.Category_ID=c.CategoryID

)

select CategoryID, CategoryName, COUNT(Article_ID) [Count] from tempTable TT join Category G on G.CategoryID=TT.Category_ID group by G.CategoryID, CategoryName order by CategoryID

Answer 2

结果如下,即返回各个目录的文章数,包括其子目录的文章

Article_ID Category_ID CategoryID CategoryName          ParentCategoryID
----------- ----------- ----------- -------------------------------------------------- ----------------
1   1   1   DB             0
2   2   2   sql            1
3   3   3   access            1
4   4   4   tsql            2
5   5   5   network           0
6   4   4   tsql            2
7   3   3   access            1
8   6   6   vpn            5
9   4   4   tsql            2
10   7   7   join            4
11   7   7   join            4

(11 row(s) affected)

CategoryID CategoryName          Count
----------- -------------------------------------------------- -----------
1   DB             9
2   sql            6
3   access            2
4   tsql            5
5   network           2
6   vpn            1
7   join            2

(7 row(s) affected)

---

Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.

Answer 3

-- create 2 similiar tables
CREATE TABLE [dbo].[Article](
[Article_ID] [int] NOT NULL,
[Category_ID] [int] NOT NULL,
CONSTRAINT [PK_Article] PRIMARY KEY CLUSTERED
(
[Article_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
'(' 附近有语法错误。
) ON [PRIMARY]

CREATE TABLE [dbo].[Category](

[CategoryID] [int] NOT NULL,
[CategoryName] [nvarchar](50) NOT NULL,
[ParentCategoryID] [int] NOT NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
'(' 附近有语法错误。
) ON [PRIMARY]

-- to see all the records

select * from Article join Category on Article.Category_ID=Category.CategoryID;

-- <strong>answer to your question: query the count by each category, including its sub categories 你的问题的答案</strong>

with tempTable as    在关键字 'with' 附近有语法错误。
(
select Article_ID, Category_ID from Article

union all

select Article_ID, c.ParentCategoryID from tempTable T join Category c on T.Category_ID=c.CategoryID

)

select CategoryID, CategoryName, COUNT(Article_ID) [Count] from tempTable TT join Category G on G.CategoryID=TT.Category_ID group by G.CategoryID, CategoryName order by CategoryID

----------------
有点抱歉，测试的时候，报错了。

Answer 4

由于我使用是SQL2000，所以报错，在换成SQL2005后，可以使用

谢谢