@ Symbol-Mysql中递归SELECT查询的解决方案? [英] @ Symbol - a solution for Recursive SELECT query in Mysql?

问题描述

关于Mysql中的递归SELECT查询有很多问题，但大多数答案是"Mysql中没有递归SELECT查询的解决方案".

there are a lot of questions about Recursive SELECT query in Mysql, but most of answers is that "There NO solution for Recursive SELECT query in Mysql".

实际上有一定的解决方案，我想清楚地知道这一点，因此该问题是在(

Actually there is a certain solution & I want to know it clearly, so this question is the following of the previous question that can be found at (how-to-do-the-recursive-select-query-in-mysql)

假设您有此表:

col1 - col2 - col3
1    -  a   -  5
5    -  d   -  3
3    -  k   -  7
6    -  o   -  2
2    -  0   -  8

&您想要在col1中找到所有连接到值"1"的链接，即您要打印出:

& you want to find all the links that connect to value "1" in col1, i.e. you want to print out:

1 - a - 5
5 - d - 3
3 - k - 7

然后您可以使用以下简单查询:

Then you can use this simple query:

select col1, col2, @pv:=col3 as 'col3' from table1
join
(select @pv:=1)tmp
where col1=@pv

好的，但是，如果您的表有2条记录的col1&中包含"1"， 2条在col1中包含"3"的记录，例如:

Ok, good, however, if your table has 2 records containing "1" in col1 & 2 records containing "3" in col1, ex:

col1 - col2 - col3
1    -  a   -  5
1    -  m   -  9
5    -  d   -  3
3    -  k   -  7
6    -  o   -  2
3    -  v   -  10
2    -  0   -  8

然后，当用户在col1中搜索"1"时，它应显示连接到2个"1"的所有链接，即应显示以下预期结果:

Then, when users search for "1" in col1, it should show all the links connecting to 2 "1", i.e. it should show this expecting result:

col1 - col2 - col3
1    -  a   -  5
1    -  m   -  9
5    -  d   -  3
3    -  k   -  7
3    -  v   -  10

所以，我的问题是我们如何修改上面的查询，以便它能像上面的预期结果一样显示所有链接?

编辑:@ Gordon， 但是，如果我们省略select distinct col1, col2 from，那么此查询意味着一些事情，您可以对此进行处理(由于childID增加了，因此我们可以对table1进行排序):

@ Gordon, but if we omit select distinct col1, col2 from then this query means something, can you work on this (since the childID got increased, so we can order the table1 ):

select col1, col2,
         @pv:=(case when find_in_set(col3, @pv) then @pv else concat(@pv, ',', col3) 
               end) as 'col3'
  from (select * from table1 order by col1) tb1 join
      (select @pv:='1') tmp
      on find_in_set(col1, @pv) > 0

在这种情况下，我们不必担心顺序，例如，如果这是数据:

In this case, we don't worry about the order, for example, if this is the data:

col1 - col2 - col3
4    -  a   -  5
1    -  d   -  2
1    -  k   -  4
2    -  o   -  3
6    -  k   -  8
8    -  o   -  9

输出将是:

col1 - col2 - col3
1    -  d   -  1,2
1    -  k   -  1,2,4
2    -  o   -  1,2,4,3

所以我们得到这个结果1,2,4,3对吗? &如果col1在1,2,4,3中，我们只选择所有记录.然后，我们可以获得最终的预期结果.

So we get this result 1,2,4,3 right? & we just select all records if the col1 is in 1,2,4,3. Then we can get the final expected result.

如果是这种情况，您能想到任何排除我刚才提到的解决方案的特殊情况吗?

If that is the case, can you think of any special case that rules out the solution I just mentioned?

推荐答案

我一直在想这样的事情是否可行:

I keep wondering if something like this would work:

select distinct col1, col2
from (select col1, col2,
             @pv:=(case when find_in_set(col3, @pv) then @pv else concat(@pv, ',', col3) 
                   end) as 'col3'
      from table1 join
          (select @pv:='1') tmp
          on find_in_set(col1, @pv) > 0
     ) t

类似的事情应该适用于小型数据集.但是，将所有id放在字符串中的想法仅限于字符串的容量.

Something like this should work for small data sets. However, the idea of putting all the ids in a string is limited to the capacity of a string.

这篇关于@ Symbol-Mysql中递归SELECT查询的解决方案?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！