mysql - 将mysql中的列分成多列

Question

我对 mysql 完全陌生。在这里，我尝试在 mysql 中进行查询，该查询根据其类别(col2)按排序顺序将列 col1 分为 4 个不同的列如下所示。到目前为止我已经写了一个这样的查询:

select if(category = 'first',name ,NULL) as first,
if(category = 'second',name,NULL) as second,
if(category = 'third',name,NULL) as third,
if(category = 'fourth',name,NULL) as fourth
from 'table';

这段代码给了我四列，但我现在陷入困境，因为我无法进一步过滤它。

给定表:

name     category
John     first
Sunil    third
Jenny    third
Ashley   fourth
Meera    second
Abhay    first

必填答案:

col1    col2    col3    col4
Abhay   Meera   Jenny   Ashley
John    NULL    Sunil   NULL

请注意，答案中的所有列均已排序。

Edit: I guess the question is not clear about the format of final answer. Thanks @philipxy for pointing out. Final answer should be adjusted in least number of rows (which is 2 in my case). All the columns should have equal number of rows and if some column has lesser values then that row will have NULL value in respective columns e.g col2 and col 4 above. Finally all the columns should be in sorted order where NULL will be in the last(if any)e.g Suppose there was an entry named Olly with category fourth then it should appear before NULL in col4 and after Ashley.

Answer 1

这很棘手。您正在尝试垂直堆叠列表，而不是水平堆叠，这不是“正常”SQL 操作。

您可以通过使用条件聚合来完成您想要的操作。问题是没有什么可以聚合的依据。解决方案是引入一个变量列来计算一个序列号进行聚合:

select max(case when category = 'first' then name end) as first,
       max(case when category = 'second' then name end) as second,
       max(case when category = 'third' then name end) as third,
       max(case when category = 'fourth' then name end) as fourth
from (select t.*,
             (@rn := if(@c = category, @rn + 1,
                        if(@c := category, 1, 1)
                       )
             ) as rn
      from `table` t cross join
           (select @c := '', @rn := 0) params
      order by category
     ) t
group by rn;

如果您希望每列中的值按特定顺序排列，请在category之后添加第二个排序键。

编辑:

我应该注意，如果您不需要多行，而只需要值，则可以将它们连接在一起:

select group_concat(case when category = 'first' then name end) as firsts,
       group_concat(case when category = 'second' then name end) as seconds,
       group_concat(case when category = 'third' then name end) as thirds,
       group_concat(case when category = 'fourth' then name end) as fourths
from`table` t
group by rn;