mysql - 您可以在将特定行值提取到选定列时进行 GROUP BY

Question

我有时会遇到这样一种情况，我有一个parent/child关系，我想为每个parent选择所有的children。

这是一个肤浅的例子。

SELECT parent.id as parent_id, child.id as child_id
FROM parent LEFT JOIN child ON parent.id = child.parent_id
ORDER BY parent.id

会产生

parent_id  child_id
1          20
1          21
2          33
2          67

我想做类似的事情

SELECT parent.id, child1.id as child1_id, child2.id as child2_id
FROM parent LEFT JOIN child ON parent.id = child.parent_id
GROUP BY parent.id

和产量

parent_id  child1_id  child2_id
1          20         21
2          33         67

我试图避免使用 GROUP_CONCAT，因为我想为每个 child 创建单独的列。

我意识到我可以加入 child 两次并过滤选择，但考虑到我的实际数据集，它可能会变得毛茸茸的加入两次。此外，如果您可以对任意数量的 child 执行此操作，那就太棒了，例如:

parent_id  child1_id  child2_id  child3_id
1          20         21         null
2          33         67         109
3          45         null       null

Answer 1

您正在寻找的是 PIVOT。 MySQL 不支持PIVOT 命令，但是您可以使用MAX 和CASE 来模拟它。

如果您知道 child 的数量或者您可以有最大数量，这将很有用。

SELECT parent.id as parent_id, 
  MAX(CASE WHEN rn = 1 THEN child.id END) child1_id,
  MAX(CASE WHEN rn = 2 THEN child.id END) child2_id,
  MAX(CASE WHEN rn = 3 THEN child.id END) child3_id,
  MAX(CASE WHEN rn = 4 THEN child.id END) child4_id,
  MAX(CASE WHEN rn = 5 THEN child.id END) child5_id
FROM parent 
  LEFT JOIN (
    SELECT *, 
      @rn:=IF(@prevParent=parent_id,@rn+1,1) rn,
      @prevParent:=parent_id
    FROM child JOIN (SELECT @rn:=0,@prevParent:=0) t
) child ON parent.id = child.parent_id
GROUP BY parent.id
ORDER BY parent.id;

• SQL Fiddle Demo

---

如果您不知道子列/潜在列的数量，则需要考虑创建动态 SQL。

这是一个例子:

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(rn = ', rn, ',child.id,NULL)) AS child_id', rn)
  ) INTO @sql
FROM parent 
  LEFT JOIN (
    SELECT *, 
      @rn:=IF(@prevParent=parent_id,@rn+1,1) rn,
      @prevParent:=parent_id
    FROM child JOIN (SELECT @rn:=0,@prevParent:=0) t
) child ON parent.id = child.parent_id
;

SET @sql = CONCAT('SELECT parent.id as parent_id, 
                          ', @sql, ' 
                  FROM parent 
                    LEFT JOIN (
                      SELECT *, 
                        @rn:=IF(@prevParent=parent_id,@rn+1,1) rn,
                        @prevParent:=parent_id
                      FROM child JOIN (SELECT @rn:=0,@prevParent:=0) t
                  ) child ON parent.id = child.parent_id
                  GROUP BY parent.id
                  ORDER BY parent.id;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

• More Fiddle