带有字符串值的mysql数据透视表 [英] mysql pivot table with string values

问题描述

我有一个只有两列的表格

I have a table with just two columns like this

mysql> select * from stuff_table;
+------+-------+
| name | stuff |
+------+-------+
| John | shoes |
| Jim  | bag   |
| Ely  | book  |
| Tom  | bag   |
| Will | shoes |
| Pitt | book  |
| Dean | bag   |
| Luke | bag   |
+------+-------+

我尝试了很多我发现的解决方案

and I tried with many solutions I found like

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 ..)或分组方式，但是它不起作用.是否有任何mysql查询来获得该表?请帮助.谢谢.

I have tried with sum(case...) or if(case..) or group by too but it doesn't work. Is there any mysql query to get such that table? Kindly help. Thank you.

推荐答案

根据所用mysql的版本，以下是一种方法:每组建立一个row_number，然后使用按该行号分组的conditional aggregation :

Depending on the version of mysql you are using, here's one approach establishing a row_number per group, then using conditional aggregation grouped by that row number:

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

• 小提琴演示
• Fiddle Demo

由于您使用的是mysql的旧版本，因此需要使用user-defined variables来确定行号.其余的工作原理相同.这是一个示例:

Since you are using an older version of mysql, you'll need to use user-defined variables to establish the row number. The rest then works the same. Here's an example:

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

• 更多小提琴
• More Fiddle

这篇关于带有字符串值的mysql数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！