积极答复者
一个无限类别表,一个文章表,如何统计任何一个类别下的全部文章数

问题
-
类别表 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)- 已编辑 南方一叶 2010年8月1日 6:12 补充
答案
-
-- 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
- 已编辑 Dannol Liu - MSFTMicrosoft employee 2010年8月2日 6:54
- 已标记为答案 南方一叶 2010年8月3日 11:47
全部回复
-
-- 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
- 已编辑 Dannol Liu - MSFTMicrosoft employee 2010年8月2日 6:54
- 已标记为答案 南方一叶 2010年8月3日 11:47
-
结果如下,即返回各个目录的文章数,包括其子目录的文章
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. -
-- 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----------------
有点抱歉,测试的时候,报错了。