douzhan1994 2013-11-05 08:07
浏览 64
已采纳

Mysql中的递归SELECT查询?

I got two following tables

category:(one to many-----question)

id name  parentId
1  test1  0
2  test2  1
3  test3  1
4  test4  3 .....

like a tree

test1
  test2
  test3
    test4

question:

id   title      category_id
1   question1    1
2   ....         1
3   ....         2

my question is : if i search category id = 1 ,there will be print total questions:

count(question.id)
     3

How to build Select query to do that? is that possible?

Thank you for your valuable help.

  • 写回答

3条回答 默认 最新

  • dtsfnyay300457 2013-11-05 08:08
    关注

    This will return count of questions for your selected category, here in example category_id = 1:

    mysqli_query('SELECT COUNT(id) FROM question WHERE category_id = 1');
    

    Updated: so, if you want to count it also for subcategories, the simpliest way will be to have "path" in your category table, where will be all IDs (self ID and ID-s of all parents), and you can separate it with ~ (its important to have ~ also at the beginning and end of path; path can be VARCHAR(255), but if you want have really deep tree, you can use TEXT.

    id name   parentId  path
    1  test1  0         ~1~
    2  test2  1         ~1~2~
    3  test3  1         ~1~3~
    4  test4  3         ~1~3~4~
    

    Hope, its clear enough, how you will update your table category to have there also column path.

    And the select then will be:

    mysqli_query('
      SELECT COUNT(id)
      FROM question
      WHERE category_id IN (
        SELECT id
        FROM category
        WHERE path LIKE "%~'.$category_id.'~%"
      )
    ');
    

    $category_id will be ID of category, for which you want to count questions (also for subcategories).

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥15 state显示变量是字符串形式,但是仍然红色,无法引用,并显示类型不匹配
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题
  • ¥20 在虚拟机的pycharm上
  • ¥15 jupyterthemes 设置完毕后没有效果
  • ¥15 matlab图像高斯低通滤波