如何在sql中的单个查询中获取类别和子类别? (mysql) [英] How to fetch categories and sub-categories in a single query in sql? (mysql)

问题描述

我想知道是否有可能在单个DB提取中提取类别和子类别.

我的数据库表与下面显示的类似

表

cat_id parent_id
1      0
2      1
3      2
4      3
5      3
6      1

即当输入为3时，则应提取parent_id为3的所有行，第3行本身以及第3行的所有父级.

输出

cat_id parent_id
3      2   -> The row 3 itself
4      3   -> Row with parent as 3
5      3   -> Row with parent as 3
2      1   -> 2 is the parent of row 3
1      0   -> 1 is the parent of row 2

这可以使用存储过程和循环来完成吗?如果是这样，它将是单个数据库读取还是多个数据库读取?还是还有其他更好的方法?

谢谢！！！

解决方案

如果您询问"mysql递归查询中是否存在?"回答否".

但是有很好的处理方法.

创建助手表(说CatHierarchy)

CatHierarchy:
    SuperId, ChildId, Distance
------------------------------ 
     1          1         0
     1          2         1
     2          2         0

此冗余数据可以轻松地在1个查询中选择任何层次结构，并在2个插入中支持任何层次结构(在1个查询中还通过删除级联完整性来执行删除操作).

那是什么意思.您跟踪层次结构中的所有路径. Cat的每个节点必须添加对自身的引用(距离0)，然后通过添加有关链接的节点的冗余数据来支持复制.

要选择带有子类别，只需输入:

 SELECT c.* from Category c inner join CatHierarchy ch ON ch.ChildId=c.cat_id
      WHERE ch.SuperId = :someSpecifiedRootOfCat

someSpecifiedRootOfCat-是用于指定类别根目录的参数 就是这样！

I would like to know if it's possible to extract the categories and sub-categories in a single DB fetch.

My DB table is something similar to that shown below

table

cat_id parent_id
1      0
2      1
3      2
4      3
5      3
6      1

i.e. when the input is 3, then all the rows with parent_id as 3 AND the row 3 itself AND all the parents of row 3 should be fetched.

output

cat_id parent_id
3      2   -> The row 3 itself
4      3   -> Row with parent as 3
5      3   -> Row with parent as 3
2      1   -> 2 is the parent of row 3
1      0   -> 1 is the parent of row 2

Can this be done using stored procedures and loops? If so, will it be a single DB fetch or multiple? Or are there any other better methods?

Thanks!!!

解决方案

If you asking about "Is there in mysql recursive queries?" answer "NO".

But there is very good approach to handle it.

Create helper table (saying CatHierarchy)

CatHierarchy:
    SuperId, ChildId, Distance
------------------------------ 
     1          1         0
     1          2         1
     2          2         0

This redundant data allows easily in 1 query to select any hierarchy, and in 2 insert support any hierarchy (deletion also performed in 1 query with help of delete cascade integrity).

So what does this mean. You track all path in hierarchy. Each node of Cat must add reference to itself (distance 0), then support duplication by adding redundant data about nodes are linked.

To select category with sub just write:

 SELECT c.* from Category c inner join CatHierarchy ch ON ch.ChildId=c.cat_id
      WHERE ch.SuperId = :someSpecifiedRootOfCat

someSpecifiedRootOfCat - is parameter to specify root of category THATS ALL!

这篇关于如何在sql中的单个查询中获取类别和子类别? (mysql)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！