问题描述
我需要在8个表中进行SELECT，但是结果却出乎我的意料.
I need to do a SELECT in 8 tables, but the results in not what I have expected.
难看的代码:
SELECT equipment.*
FROM equipment
LEFT JOIN equip_adaptador a ON (a.cod_equip = equipment.cod_equip)
LEFT JOIN equip_antena aa ON (aa.cod_equip = equipment.cod_equip)
LEFT JOIN equip_cable c ON (c.cod_equip = equipment.cod_equip)
LEFT JOIN equip_conector cc ON (cc.cod_equip = equipment.cod_equip)
LEFT JOIN equip_fonte f ON (f.cod_equip = equipment.cod_equip)
LEFT JOIN equip_router r ON (r.cod_equip = equipment.cod_equip)
LEFT JOIN equip_suporte s ON (s.cod_equip = equipment.cod_equip)
WHERE equipment.cod_equip = 'EC726026316A0'
结果是63个项目，不对.
解释上面的代码:
我的表equipment是我的主表，这里有cod_equip field(所有从属表的主字段).
My table equipment is my master table, there I have the cod_equip field (a master field to all my slave tables).
我所有的从属表我给了一个叫equip_的前缀(总共有7个从属表)
All my slave table I had gave a prefix called equip_ (are 7 slave tables in the total)
现在我需要SELECT到JOIN所有8张桌子.
Now I need a SELECT to JOIN all the 8 tables.
添加更多:
我原本希望有9行，但它却获取了63行，我需要显示以下内容:表设备(仅1行)，而其他表则拥有它的尊敬数.
I was expecting 9 rows, but it fetched 63 rows, I need to show something like this: table equipment (only 1 row) and other tables the respect number of it owns.
例如 equip_adaptador 被两次插入相同的 cod_equip ，那么我需要显示它.
For example equip_adaptador was two times inserted with the same cod_equip, then I need to show it..
此查询与我逐个查询以查看 equipment.cod_equip ='EC726026316A0'
This query is the same as if I do one by one query to see what I have with the equipment.cod_equip = 'EC726026316A0'
就这样！
先谢谢大家！
推荐答案
正在发生的事情是，子表返回的每一行都与其他子表返回的每一行匹配.
What's happening is that each row returned from a child table is matched with every row returned from the other child tables.
如您所料，您返回了一个父行.
You have one parent row returned, as you expect.
但是，如果子表之一具有七(7)个匹配行，而另一个子表具有九(9)匹配行，则返回7 * 9 = 63行.
But if one of the child tables has seven (7) matching rows, and another child table has nine (9) matching rows, you are getting 7*9=63 rows returned.
根据SQL规范，这是预期的结果集.
This is the expected resultset, per the SQL specification.
下面是一个测试案例，演示了正在发生的事情:
Here is a test case that demonstrates what is happening:
CREATE TABLE t (id INT);
CREATE TABLE c1 (id INT, t_id INT);
CREATE TABLE c2 (id INT, t_id INT);
INSERT INTO t VALUES (1);
INSERT INTO c1 VALUES (11,1),(12,1);
INSERT INTO c2 VALUES (21,1),(22,1);
SELECT t.id, c1.id AS c1, c2.id AS c2
FROM t
JOIN c1 ON (t.id = c1.t_id)
JOIN c2 ON (t.id = c2.t_id)
id c1 c2
-- --- ---
1 11 21
1 12 21
1 11 22
1 12 22
请注意，对于来自c2的每一行，都重复来自c1的行.对于来自c2的行也是如此.
Note that the rows from c1 are repeated, once for each row from c2. Likewise for the rows from c2.
这正是我们期望的结果集.
This is exactly the result set we expect.
如果我们不希望子行的交叉连接(笛卡尔积)，我们可以运行单独的查询:
If we don't want a cross join (cartesian product) of the child rows, we can run separate queries:
SELECT t.id, c1.id AS c1
FROM t
JOIN c1 ON (t.id = c1.t_id)
SELECT t.id, c2.id AS c2
FROM t
JOIN c2 ON (t.id = c2.t_id)
id c1
-- ---
1 11
1 12
id c2
-- ---
1 21
1 22
这是避免生成重复的"子行的一种方法.
That's one way to avoid generating "repeated" child rows.
这篇关于多表MySQL查询返回太多结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！