mysql移动列列_如何在MySQL中移动列值？

使用coalesce()和子查询

select id, o1,

CASE WHEN o2!=o1 THEN o2 END o2,

CASE WHEN o3!=o2 THEN o3 END o3

FROM

( select id, coalesce(org1,org2,org3) o1,

coalesce(org2,org3) o2,

org3 o3 from tbl ) t

UPDATE

之前的答案还不够,因为R2D2发现得非常正确.不幸的是你不能在mysql中做CTE,所以我创建了一个视图(我通过另一列org4扩展了示例)：

CREATE VIEW vert AS

select id i,1 n, org1 org FROM tbl where org1>'' UNION ALL

select id,2, org2 FROM tbl where org2>'' UNION ALL

select id,3, org3 FROM tbl where org3>'' UNION ALL

select id,4, org4 FROM tbl where org4>'';

有了这个视图,现在可以执行以下操作：

SELECT id,

(select org from vert where i=id order by n limit 1) org1,

(select org from vert where i=id order by n limit 1,1) org2,

(select org from vert where i=id order by n limit 2,1) org3,

(select org from vert where i=id order by n limit 3,1) org4

FROM tbl

不是很漂亮,但它完成了工作,请看这里：SQLfiddle

输入：

| id | org1 | org2 | org3 | org4 |

|----|--------|--------|---------|--------|

| 1 | HR | (null) | Staff | IT |

| 2 | (null) | IT | Dev | (null) |

| 3 | (null) | (null) | Finance | HR |

输出：

| id | org1 | org2 | org3 | org4 |

|----|---------|-------|--------|--------|

| 1 | HR | Staff | IT | (null) |

| 2 | IT | Dev | (null) | (null) |

| 3 | Finance | HR | (null) | (null) |