none
一个无限类别表,一个文章表,如何统计任何一个类别下的全部文章数 RRS feed

  • 问题

  • 类别表  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:07

答案

  • -- 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
    
    2010年8月2日 6: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
    
    2010年8月2日 6: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.
    2010年8月2日 6:53
  • -- 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

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

    2010年8月2日 7:49
  • 由于我使用是SQL2000,所以报错,在换成SQL2005后,可以使用

    谢谢

    2010年8月3日 11:48