MySQL:将另一个表中的2行合并为一个结果行

Question

这些是假表名，但在我的实际问题中，想象一条业务规则，规定每个 parent 都有 1 或 2 个 child 。这些子级的编号为 0 或 1。同一父级的两个子级不能具有相同的编号。

我想要访问 children 行中的字段，但希望将这些值放入数字前缀字段中。示例:

父子关系:

+-------------+-------------+
|    parent   |    child    |
+-------------+-------------+
|      1      |      1      |
|      1      |      2      |
|      2      |      3      |
|      3      |      4      |
+-------------+-------------+

children :

+-------------+-------------+------------+
|    child    |    number   |    name    |
+-------------+-------------+------------+
|      1      |      0      |   Chuck    |
|      2      |      1      |   Sally    |
|      3      |      0      |   Carly    |
|      4      |      1      |   Billy    |
+-------------+-------------+------------+

期望的结果:

+-------------+-------------+------------+
|   parent    |    child0   |   child1   |
+-------------+-------------+------------+
|      1      |    Chuck    |    Sally   |
|      2      |    Carly    |     NULL   |
|      3      |     NULL    |    Billy   |
+-------------+-------------+------------+

我能得到的最接近的结果如下:

SELECT A.parent, C0.name as child0, C1.name as child1
FROM parenttochildassoc A
    LEFT JOIN children C0 ON C0.child = A.child AND C0.number = 0
    LEFT JOIN children C1 ON C1.child = A.child AND C1.number = 1

产生这个:

+-------------+-------------+------------+
|   parent    |    child0   |   child1   |
+-------------+-------------+------------+
|      1      |    Chuck    |     NULL   |
|      1      |     NULL    |    Sally   |
|      2      |    Carly    |     NULL   |
|      3      |     NULL    |    Billy   |
+-------------+-------------+------------+

我怎样才能把它弄平？它看起来与此answer中描述的“枢轴”非常相似。 ，(建议的答案之一与我的尝试相同)但 parenttochildrenassoc 是多对一的事实似乎打破了它。

Answer 1

实际上，您可以通过条件聚合和一次连接来做到这一点:

SELECT a.parent,
       MAX(CASE WHEN c.number = 0 THEN c.name END) as child0,
       MAX(CASE WHEN c.number = 1 THEN c.name END) as child1
FROM parenttochildassoc a LEFT JOIN
     children c
     ON C0.child = A.child
GROUP BY a.parent;

您可以轻松地将其扩展到您想要的任意数量的 child 。

如果您可以在一个字段中容纳所有子级，请使用group_concat():

SELECT a.parent,
       GROUP_CONCAT(c.name ORDER BY c.number) as children
FROM parenttochildassoc a LEFT JOIN
     children c
     ON C0.child = A.child
GROUP BY a.parent;