多表MySQL查询返回太多结果 [英] Multi-table MySQL query returning too many results

问题描述

我需要在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查询返回太多结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！