MySQL - 将多行包装成一行

Question

我在 MySQL 中有这个数据集，其中的记录需要折叠成一行，但是要环绕的列数各不相同。这是一个例子:

Id     Name                  Pre/Post    Attempt     Other Data
--     --------              --------    -------     ----------
1      James Owens              Pre         1          blah
1      James Owens              Post        1          blah
1      James Owens              Post        2          blah

2      Derek Williams           Post        1          blah
2      Derek Williams           Post        2          blah
2      Derek Williams           Post        3          blah

3      Sean Parker              Pre         1          blah

4      Miles Taylor             Pre         1          blah
4      Miles Taylor             Post        1          blah
4      Miles Taylor             Post        2          blah
4      Miles Taylor             Post        3          blah
4      Miles Taylor             Post        4          blah

最大尝试次数为 4。我希望数据以这种方式结束(或接近此方式):

Id     Name                     Pre         Post      Post      Post      Post      Other Data
--     -------------            ---         ----      ----      ----      ----      ----------
1      James Owens               1           1         2                            blah
2      Derek Williams                        1         2         3                  blah
3      Sean Parker               1                                                  blah
4      Miles Taylor              1           1         2         3         4        blah

我知道您会问“您尝试过什么？” ——好吧，我还没有尝试过任何事情，因为我不知道从哪里开始。

有人能指出我正确的方向吗？

Answer 1

正如 Ryan 在评论中提到的，您可以只有两列来存储尝试前和尝试后的计数。您可以通过以下方式实现此目的:

select distinct(tbl.id), name, pre_count.count, post_count.count, other_data
from `tbl` left join (select id, count(*) as count
                      from `tbl` where pre_post = 'pre' group by id) as pre_count
                      on tbl.id = pre_count.id
           left join (select id, count(*) as count
                      from `tbl` where pre_post = 'post' group by id) as post_count
                      on tbl.id = post_count.id