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

带有字符串值的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) tgroup 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) tgroup by rn