MySQL如何分组为行的子组

Question

我有这个数据库:(注意 id 是唯一的)

我想得到这个:

实现此目标的高效 SQL 查询是什么？

Answer 1

SELECT 
    DISTINCT
    foo2.group_id AS group_id,
    if(foo1.group_id = foo2.group_id, foo1.id, NULL) AS id,
    if(foo1.group_id = foo2.group_id, foo1.some_attr, NULL) AS some_attr
 FROM mytable AS foo1
    JOIN
    (SELECT DISTINCT group_id FROM mytable) as foo2        
    ORDER BY group_id, id