假设我们有多个子表child1, child2, child3...
与一个父表具有一对一的关系parent
。
---------- ---------- ---------- ----------
| parent | | child1 | | child2 | | child3 |
|--------| |--------| |--------| |--------|
|parentID| |child1ID| |child2ID| |child3ID|
---------- |parentID| |parentID| |parentID|
---------- ---------- ----------
将所有相关数据保存到parentID的一条记录中的最佳方法是什么?
我尝试了一下,LEFT JOIN
但是结果却导致了很多不必要的行(所有组合,numRows父级x numRows child1 x numRows child2 x numRows child3)。因此,对于一堆真实的数据行,我得到了非常快的数千行:
SELECT
child1.child1ID,
child2.child2ID,
child3.child3ID
FROM parent
LEFT JOIN child1 ON child1.parentID = parent.parentID,
LEFT JOIN child2 ON child2.parentID = parent.parentID,
LEFT JOIN child3 ON child3.parentID = parent.parentID
WHERE parent.parentID = 1;
这是预期的方式吗?据我了解,以下情况发生在这里:
但这感觉有点不对劲,因为子表只能连接(联接)到父表。子表之间没有关系。
结果如下(如果所有子表都有2行):
child1ID | child2ID | child3ID
1 | 1 | 1
1 | 1 | 2
1 | 2 | 1
1 | 2 | 2
2 | 1 | 1
2 | 1 | 2
2 | 2 | 1
2 | 2 | 2
因此,我们有8行(2 x 2 x 2)。随着更多的行和更多的子表,计数可以轻松地进入数百万行。但我想要这样的结果:
child1ID | child2ID | child3ID
1 | NULL | NULL
2 | NULL | NULL
NULL | 1 | NULL
NULL | 2 | NULL
NULL | NULL | 1
NULL | NULL | 2
在这里,我们只有6行(2 + 2 + 2),即使有更多的子表,行数也不会爆炸。
我怎样才能做到这一点?
顺便说一句,我也查看UNION
并使用了3个选择,但由于子表的表结构不相同(不同的列数和数据类型),所以这GROUP BY
行不通,这似乎也不是一种好方法,因为查询仍然在进行分组之前先获取数百万行。
我找到了解决方案!
不仅可以通过childN.foreignKey = parentTable.parentKey联接行,而且还可以通过行号联接行:
SELECT
T1.child1ID,
T2.child2ID,
T3.child3ID
FROM parent
LEFT JOIN (SELECT *, ROW_NUMBER() OVER() as rownum FROM child1) as T1 ON T1.parentID = parent.parentID
LEFT JOIN (SELECT *, ROW_NUMBER() OVER() as rownum FROM child2) as T2 ON T2.parentID = parent.parentID AND T2.rownum = T1.rownum
LEFT JOIN (SELECT *, ROW_NUMBER() OVER() as rownum FROM child3) as T3 ON T3.parentID = parent.parentID AND T3.rownum = T1.rownum
WHERE parent.parentID = 1;
重要说明:由于MySQL不支持FULL OUTER JOIN
,第一个连接的表必须是行数最多的表。
结果:
child1ID | child2ID | child3ID
1 | 1 | 1
2 | 2 | 2
如果我们有多个具有不同行数的表,但是具有最多行的表是第一个联接的表,那么我们得到的结果是这样的:
child1ID | child2ID | child3ID | child4ID | child5ID | child6ID
1 | 1 | 1 | 1 | 1 | 1 |
2 | 2 | 2 | NULL | 2 | 2 |
3 | NULL | 3 | NULL | 3 | 3 |
4 | NULL | NULL | NULL | 4 | NULL |
5 | NULL | NULL | NULL | NULL | NULL |
我希望它对尝试将多个表联接到一个父表而不获取所有组合的其他用户也有所帮助。