ok, Her is my problem. I got a table that has 3 columns: C1, C2, C3
好的,她是我的问题。我有一个有3列的表:C1,C2,C3
C1 - C2 - C3 M - is - M1 N2 - is - N3 M2 - is - M3 M3 - is - M4 N1 - is - N2 M1 - is - M2 N - is - N1
So when user search for "M1", the system will show:
因此,当用户搜索“M1”时,系统将显示:
M1 M2 M3 M4
when user search for "M3", the system will show:
当用户搜索“M3”时,系统会显示:
M3 M4
when user search for "N", the system will show:
当用户搜索“N”时,系统将显示:
N N1 N2 N3
Clearly, this kind of query requires some sort of looping, ie searching for any word after "is", then continuously searching for word after that word.
显然,这种查询需要某种循环,即在“是”之后搜索任何单词,然后在该单词之后不断搜索单词。
I wanna use normal "Select SQL" in MySQL rather than using Stored Procedure to do this task. But I have no clue how to do.
我想在MySQL中使用普通的“选择SQL”,而不是使用存储过程来执行此任务。但我不知道该怎么做。
Do we have to use "WITH" keyword?
我们必须使用“WITH”关键字吗?
How to do the above query without using Stored Procedure?
如何在不使用存储过程的情况下执行上述查询?
2 个解决方案
#1
2
So, sticking to the requirement of NOT using a stored procedure, the following query may be used:
因此,坚持NOT使用存储过程的要求,可以使用以下查询:
SELECT t1.C1 AS Value1, t2.C1 AS Value2, t3.C1 AS Value3, t4.C1 AS Value4
FROM your_table t1
LEFT JOIN your_table t2 ON t1.C3=t2.C1
LEFT JOIN your_table t3 ON t2.C3=t3.C1
LEFT JOIN your_table t4 ON t3.C3=t4.C1
It will return results such as M1 M2 M3 M4. If you need to "loop" more, you may add another LEFT JOIN to the query:
它将返回M1 M2 M3 M4等结果。如果需要“循环”更多,可以在查询中添加另一个LEFT JOIN:
SELECT t1.C1 AS Value1, t2.C1 AS Value2, t3.C1 AS Value3, t4.C1 AS Value4, t5.C1 AS Value5
FROM your_table t1
LEFT JOIN your_table t2 ON t1.C3=t2.C1
LEFT JOIN your_table t3 ON t2.C3=t3.C1
LEFT JOIN your_table t4 ON t3.C3=t4.C1
LEFT JOIN your_table t5 ON t4.C3=t5.C1
You will however be limited in the number of tables you can join this way:
但是,您可以通过这种方式加入表格数量:
The maximum number of tables that can be referenced in a single join is 61.
单个连接中可引用的最大表数为61。
You will need to use server-side language (PHP?) to check if Value2, Value3...ValueN are null or not.
您需要使用服务器端语言(PHP?)来检查Value2,Value3 ... ValueN是否为null。
My advice is to use a stored procedure, or to change your table schema to avoid having to loop in your tables this way.
我的建议是使用存储过程,或更改表模式,以避免以这种方式循环表。
#2
0
You could accomplish this task using a recursive CTE; unfortunately, mysql does not yet support these.
您可以使用递归CTE完成此任务;不幸的是,mysql还不支持这些。
However, I submit to you that you have a larger problem in that you're implementing the classic SQL Antipattern referred to as a "Naive Tree"; I strongly recommend you review the slidedeck SQL Anti-Patterns Strike Back - especially the section beginning around slide 48, where the author will provide viable alternatives that will make your problem easier to encapsulate in the subset of SQL your database supports.
但是,我向你提出,你有一个更大的问题,你正在实现被称为“天真树”的经典SQL反模式;我强烈建议您查看幻灯片SQL反模式反击 - 特别是从幻灯片48开始的部分,作者将提供可行的替代方案,使您的问题更容易封装在您的数据库支持的SQL子集中。
#1
2
So, sticking to the requirement of NOT using a stored procedure, the following query may be used:
因此,坚持NOT使用存储过程的要求,可以使用以下查询:
SELECT t1.C1 AS Value1, t2.C1 AS Value2, t3.C1 AS Value3, t4.C1 AS Value4
FROM your_table t1
LEFT JOIN your_table t2 ON t1.C3=t2.C1
LEFT JOIN your_table t3 ON t2.C3=t3.C1
LEFT JOIN your_table t4 ON t3.C3=t4.C1
It will return results such as M1 M2 M3 M4. If you need to "loop" more, you may add another LEFT JOIN to the query:
它将返回M1 M2 M3 M4等结果。如果需要“循环”更多,可以在查询中添加另一个LEFT JOIN:
SELECT t1.C1 AS Value1, t2.C1 AS Value2, t3.C1 AS Value3, t4.C1 AS Value4, t5.C1 AS Value5
FROM your_table t1
LEFT JOIN your_table t2 ON t1.C3=t2.C1
LEFT JOIN your_table t3 ON t2.C3=t3.C1
LEFT JOIN your_table t4 ON t3.C3=t4.C1
LEFT JOIN your_table t5 ON t4.C3=t5.C1
You will however be limited in the number of tables you can join this way:
但是,您可以通过这种方式加入表格数量:
The maximum number of tables that can be referenced in a single join is 61.
单个连接中可引用的最大表数为61。
You will need to use server-side language (PHP?) to check if Value2, Value3...ValueN are null or not.
您需要使用服务器端语言(PHP?)来检查Value2,Value3 ... ValueN是否为null。
My advice is to use a stored procedure, or to change your table schema to avoid having to loop in your tables this way.
我的建议是使用存储过程,或更改表模式,以避免以这种方式循环表。
#2
0
You could accomplish this task using a recursive CTE; unfortunately, mysql does not yet support these.
您可以使用递归CTE完成此任务;不幸的是,mysql还不支持这些。
However, I submit to you that you have a larger problem in that you're implementing the classic SQL Antipattern referred to as a "Naive Tree"; I strongly recommend you review the slidedeck SQL Anti-Patterns Strike Back - especially the section beginning around slide 48, where the author will provide viable alternatives that will make your problem easier to encapsulate in the subset of SQL your database supports.
但是,我向你提出,你有一个更大的问题,你正在实现被称为“天真树”的经典SQL反模式;我强烈建议您查看幻灯片SQL反模式反击 - 特别是从幻灯片48开始的部分,作者将提供可行的替代方案,使您的问题更容易封装在您的数据库支持的SQL子集中。