带有字符串值的mysql数据透视表

朱儒之乡越南花果山

我有一个只有两列的表格
mysql> select * from stuff_table;
+------+-------+
| name | stuff |
+------+-------+
| John | shoes |
| Jim  | bag |
| Ely  | book  |
| Tom  | bag |
| Will | shoes |
| Pitt | book  |
| Dean | bag |
| Luke | bag |
+------+-------+
我尝试了很多发现的解决方案，例如
select distinct
max(case when stuff='shoes' then name else name is null) end as shoes,
max(case when stuff='bag' then name else name is null end) as bag,
max(case when stuff='book' then name else name is null end) as book
from stuff_table;
但是我刚得到这个
+-------+------+------+
| shoes | bag  | book |
+-------+------+------+
| Will  | Tom  | Pitt |
+-------+------+------+
相反，我想得到这个
+-------+------+------+
| shoes | bag  | book |
+-------+------+------+
| John  | Jim  | Ely  |
| Will  | Tom  | Pitt |
| NULL  | Dean | NULL |
| NULL  | Luke | NULL |
+-------+------+------+
我已经尝试过sum（case …）或if（case ..）或group by，但是它不起作用。是否有任何mysql查询来获得该表？请帮助。谢谢你。

解决方案:


            根据mysql您使用的版本，这是一种建立row_number每个组，然后conditional aggregation按该行号分组的方法：
select
rn,
max(case when stuff = 'bag' then name end) 'bag',
max(case when stuff = 'book' then name end) 'book',
max(case when stuff = 'shoes' then name end) 'shoes'
from (
  select *, row_number() over (partition by stuff order by name) rn
  from stuff_table
) t
group by rn
小提琴演示

由于您使用的是旧版本mysql，因此您需要使用user-defined variables来建立行号。其余的工作原理相同。这是一个例子：
select
rn,
max(case when stuff = 'bag' then name end) 'bag',
max(case when stuff = 'book' then name end) 'book',
max(case when stuff = 'shoes' then name end) 'shoes'
from (
  select *,
  ( case stuff
      when @curStuff
      then @curRow := @curRow + 1
      else @curRow := 1 and @curStuff := stuff
end
  ) + 1 AS rn
  from stuff_table, (select @curRow := 0, @curStuff := '') r
  order by stuff
) t
group by rn

带有字符串值的mysql数据透视表

朱儒之乡越南花果山 LV1