MySQL:在一个Statement上连接许多表 [英] MySQL: join many tables on one Statement
问题描述
我具有以下DB结构/层次结构:
i have the following DB-Structure / hierarchy:
表产品类型:
id,name, ....
表产品:
id,parent_id, name, ...
parent_id :是产品类型ID
TABLE treeNode :
id, parent_id, name, type
具有树层次结构(根具有n个子节点) 层次结构的数量为未知.
its a tree hierarchy (roots have n sub Nodes) the number of levels of hierarchy is unknown.
col type具有值类别" 或组" ，
那意味着我有两棵树:
the col type has the values "CATEGORY" OR "GROUP",
that means, i have 2 trees:
类别:
TreeNode 1
sub 1.1
sub.1.1.1
sub.1.1.2
....
sub 1.2
sub.1.2.1
sub.1.2.2
....
TreeNode 2
sub 2.1
sub.2.1.1
sub.2.1.2
....
sub 2.2
sub.2.2.1
sub.2.2.2
....
组:
TreeNode 1
sub 1.1
sub.1.1.1
sub.1.1.2
....
sub 1.2
sub.1.2.1
sub.1.2.2
....
TreeNode 2
sub 2.1
sub.2.1.1
sub.2.1.2
....
sub 2.2
sub.2.2.1
sub.2.2.2
....
表 linked_treeNode:
product_id, treeNode_id
现在说，用户选择:
1:一种产品类型(参数:$selected_type)
1: a product type (param: $selected_type)
2:类别(参数:$selected_cat)
3:一个组(参数:$selected_group)
3: a group (param: $selected_group)
现在，我想显示符合以下选择的所有产品:
now i would like to display all Products which meets these selections:
1-)链接到选定的类别或子类别
1-) linked to selected Catagory or to its Subcategories
AND
2-)链接到选定的组或其子组
2-) linked to selected Group or to its subGroups
AND
3-)链接到所选产品类型
3-) linked to selected product type
MySQL 语句(1条语句)是什么?
what is the MySQL statement ( 1 Statement) for that?
我尝试过:
SELECT P.* FROM
product P, treeNode C, treeNode G, linked_TreeNode LC
WHERE
p.parent_id='$selected_type'
AND
(
C.type='CATEGORY'
AND
C.parent_id='$selected_cat'
AND
P.id=LC.product_id
AND
(LC.treeNode_id=C.id OR LC.treeNode_id='$selected_cat')
)
AND
(
G.type='GROUP'
AND
G.parent_id='$selected_group'
AND
P.id=LC.product_id
AND
(LC.treeNode_id=G.id OR LC.treeNode_id='$selected_group')
)
;
但是我总是得到0个结果！
我用JOINS ..etc尝试了许多其他声明(更改).但没有成功.
i tried many other Statements(changes), with JOINS ..etc. but without success.
非常感谢
我在上面使用的语句是错误的，所以不要使用它！
推荐答案
如何在MySql中使用递归查询从树节点获取所有后代?
这对于MySql来说确实是一个问题，这是这个问题的关键，但是您仍然可以选择.
How to get all descendants from a tree node with recursive query in MySql?
It's really a problem for MySql, and it's a key point to this question, but you still have some choices.
假设您拥有这样的示例数据，虽然不如您的示例多，但足以证明:
Assuming you have such sample data, not as much as your sample but enough to demonstrate:
create table treeNode(
id int, parent_id int, name varchar(10), type varchar(10),level int);
insert into treeNode
(id, parent_id, name, type, level) values
( 1, 0, 'C1 ', 'CATEGORY', 1),
( 2, 1, 'C1.1 ', 'CATEGORY', 2),
( 3, 2, 'C1.1.1', 'CATEGORY', 3),
( 4, 1, 'C1.2 ', 'CATEGORY', 2),
( 5, 4, 'C1.2.1', 'CATEGORY', 3),
( 3, 8, 'G1.1.1', 'GROUP', 3),
( 4, 9, 'G1.2 ', 'GROUP', 2),
( 5, 4, 'G1.2.1', 'GROUP', 3),
( 8, 9, 'G1.1 ', 'GROUP', 2),
( 9, 0, 'G1 ', 'GROUP', 1);
首选:级别代码
就像treeNode表中name列的样本数据一样. (我不知道怎么说英语，请评论level code 的正确表达方式.)
First choice: level code
Like the sample data of the name column in treeNode table. (I don't know how to say it in English, comment me about the correct expression of level code.)
要获取C1或G1的所有后代，可能很简单:
To get all descendants of C1 or G1 could be simple like this:
select * from treeNode where type = 'CATEGORY' and name like 'C1%' ;
select * from treeNode where type = 'GROUP' and name like 'G1%' ;
我非常喜欢这种方法，甚至需要我们在treeNode保存到应用程序之前生成这些代码.当我们有大量记录时，它将比递归查询或过程更有效.我认为这是一种很好的非规范化方法.
I prefer this approach very much, even need us to generate these code before treeNode saved in application. It will be more efficient than recursive query or procedure when we have large number of records. I think this is a good denormalization approach.
通过这种方法，您想要与联接一起使用的语句可以是:
With this approach, the statement you want with join could be:
SELECT distinct p.* --if there is only one tree node for a product, distinct is not needed
FROM product p
JOIN product_type pt
ON pt.id= p.parent_id -- to get product type of a product
JOIN linked_TreeNode LC
ON LC.product_id= p.id -- to get tree_nodes related to a product
JOIN (select * from treeNode where type = 'CATEGORY' and name like 'C1%' ) C --may replace C1% to concat('$selected_cat_name','%')
ON LC.treeNode_id = C.id
JOIN (select * from treeNode where type = 'GROUP' and name like 'G1%' ) G --may replace G1% to concat('$selected_group_name','%')
ON LC.treeNode_id = G.id
WHERE pt.name = '$selected_type' -- filter selected product type, assuming using product.name, if using product.parent_id, can save one join by pt like your original sql
甜，不是吗?
将一个级别列附加到treeNode表，如DDL中所示.
Append a level column to treeNode table, as shown in the DDL.
级别编号更易于维护.
Level number is much easier to maintain than level code in application.
使用级别编号来获取C1或G1的所有后代需要这样的小技巧:
With level number to get all descendants of C1 or G1 need a little trick like this:
SELECT id, parent_id, name, type, @pv:=concat(@pv,',',id) as link_ids
FROM (select * from treeNode where type = 'CATEGORY' order by level) as t
JOIN (select @pv:='1')tmp
WHERE find_in_set(parent_id,@pv)
OR find_in_set(id,@pv);
-- get all descendants of `C1`
SELECT id, parent_id, name, type, @pv:=concat(@pv,',',id) as link_ids
FROM (select * from treeNode where type = 'GROUP' order by level) as t
JOIN (select @pv:=',9,')tmp
WHERE find_in_set(parent_id,@pv)
OR find_in_set(id,@pv) ;
此方法比第一种慢，但仍比递归查询快.
This approach is slower than the first, but still faster than recursive query.
省略了该问题的完整sql.只需将C和G这两个子查询替换为上面的两个查询即可.
The full sql to the question omitted. Only need to replace those two subquery of C and G with two querys above.
注意:
有许多类似的方法，例如 此处 ， 这里 .除非按级别编号或级别代码订购，否则它们将无法工作.您可以通过更改以下内容来测试此 SqlFiddle 中的最后一个查询order by level到order by id来查看差异.
There are many similar approach such as here , here, or even here. They won't work unless ordered by level number or level code. You could test the last query in this SqlFiddle by changing order by level to order by id to see the differences.
请参考此博客，我没有测试然而.但是我认为这类似于最后两个选择.
Please reference to this blog, I did not test yet. But I think it's similar to last two choice.
需要在treenode表中添加一个左数字和一个右数字，以在它们之间包含所有后代的ID.
It need you add a left number and a right number to the treenode table to enclose all descendants' ids between them.
这篇关于MySQL:在一个Statement上连接许多表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
