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

Question

我有这张表:

table1
+------+----------+-----------+----------+
|  id  | org1     | org2      | org3     |
+------+----------+-----------+----------+
|  1   | HR       | (NULL)    | Staff    |
+------+----------+-----------+----------+
|  2   | (NULL)   | IT        | Dev      |
+------+----------+-----------+----------+
|  3   | (NULL)   | (NULL)    | Finance  |
+------+----------+-----------+----------+

我想将所有值向左移动，因此最终结果将是:

table1
+------+----------+-----------+----------+
|  id  | org1     | org2      | org3     |
+------+----------+-----------+----------+
|  1   | HR       | Staff     | (NULL)   |
+------+----------+-----------+----------+
|  2   | IT       | Dev       | (NULL)   |
+------+----------+-----------+----------+
|  3   | Finance  | (NULL)    | (NULL)   |
+------+----------+-----------+----------+

有什么优雅的方法吗？

Answer 1

使用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

更新

之前的答案是不够的，因为 R2D2 发现很正确。不幸的是，你不能在 mysql 中执行 CTE，所以我创建了一个 View (我通过另一列 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>'';

有了这个 View ，现在可以执行以下操作:

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) |