mysql - 将多列 WRT 转置为 MySQL 中的一行

Question

我已经搜索过转置和枢轴，但找不到类似的东西。输入表:

+-----+-------+-------+-------+
| TID |  TP1  |  TP2  |  TP3  |
+-----+-------+-------+-------+
| A   | link1 | link1 | link3 |
| B   | link3 |       |       |
| C   | link2 |       |       |
+-----+-------+-------+-------+

所需的输出表:

+-----+--------+-------+
| TID | TP Num |  REF  |
+-----+--------+-------+
| A   |      1 | link1 |
| A   |      2 | link1 |
| A   |      3 | link3 |
| B   |      1 | link3 |
| C   |      1 | link2 |
+-----+--------+-------+

Answer 1

这有点复杂，您需要使用 union all 来获得垂直表示，然后使用用户定义的变量来获取 nums 的东西

select TID,`TP Num`,REF from (
  select TID,REF,@rn:= if (@prev_tid = TID,@rn+1,1) as `TP Num`,@prev_tid:=TID  from
  (
    select
    TID,REF from
    (
        select TID , TP1 as REF from mytable where TP1 is not null and TP1 <> ''
        union all 
        select TID , TP2 as REF from mytable where TP2 is not null and TP2 <> ''
        union all
        select TID , TP3 as REF from mytable where TP3 is not null and TP3 <> ''
    )x
  )y,(select @rn:=0,@prev_tid:=null)z
  order by TID,REF 
)x;

http://sqlfiddle.com/#!9/1d15c/14