如何在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)   |
+------+----------+-----------+----------+

有没有更优雅的方法来做到这一点？

- Artur Kędzior

---

你有修改表的权限吗？可以参考在MYSQL中交换值。 - Arun Palanisamy

我看过那个，但是想法是移位而不是交换。干杯。 - Artur Kedzior

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，因此我创建了一个视图代替它（我添加了另一列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) |

- Carsten Massmann

---

谢谢，但“Dev”不会移位。 - Artur Kedzior

将COALESCE（org2，org3）的顺序切换为COALESCE（org3，org2）即可完成工作。谢谢！ - Artur Kedzior

Answer 2

更新：

根据cars10的回答，需要交换顺序COALESCE（org2，org3）并考虑当所有3个列都NOT NULL时。

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
,IF((org1 IS NOT NULL) AND (org2 IS NOT NULL) AND (org3 IS NOT NULL),
    org2,
    COALESCE(org3,org2)
) o2
,org3 o3
FROM table1
) t

增加了cars10提到的案例：

DROP TABLE IF EXISTS table1;
CREATE TABLE `table1` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `org1` VARCHAR(255) DEFAULT NULL,
  `org2` VARCHAR(255) DEFAULT NULL,
  `org3` VARCHAR(255) DEFAULT NULL,  
  PRIMARY KEY (`id`)
);

INSERT INTO `table1` VALUES ('1', NULL, 'IT', 'DEV');
INSERT INTO `table1` VALUES ('2', 'HR',NULL,'Staff');
INSERT INTO `table1` VALUES ('3', 'ID','Building',NULL);
INSERT INTO `table1` VALUES ('4', 'Support','Business','1st line');

INSERT INTO `table1` VALUES ('5','Finance', NULL, NULL);
INSERT INTO `table1` VALUES ('6', NULL, 'Finance', NULL );
INSERT INTO `table1` VALUES ('7', NULL, NULL, 'Finance');
INSERT INTO `table1` VALUES ('8', NULL, NULL, NULL);

http://www.sqlfiddle.com/#!9/cd969/1

正如Thorsten Kettner所提到的，没有优雅的方法来解决这个问题。我发现上述方法是最简短有效的解决方案。

- Artur Kedzior

---

很不幸，您（和我修改后的原始解决方案）在设置了所有三个“org”时无法正常工作！请参见此处：http://www.sqlfiddle.com/#!9/45bfe4/1（最后一行应为'aa'，'bb'，'cc'而不是'aa'，'cc'！） - Carsten Massmann

Answer 3

一种优雅的解决方案是先将列变成行，使用分析函数对它们进行排名，然后聚合结果：

select 
  id, 
  max(case when rn = 1 then org end) as org1,
  max(case when rn = 2 then org end) as org2,
  max(case when rn = 3 then org end) as org3
from
(
  select id, org, row_number() over (partition by id order by num) as rn
  from
  (
    select id, org1 as org, 1 as num from mytable where org1 is not null
    union all
    select id, org2 as org, 2 as num from mytable where org2 is not null
    union all
    select id, org3 as org, 3 as num from mytable where org3 is not null
  ) given
) ranked
group by id;

然而，MySQL不支持分析函数。因此，在MySQL中没有纯SQL的优雅解决方案。您将不得不使用CASE WHEN和COALESCE，这只需要三列就可以快速完成，但是如果有更多列，则会相当繁琐。

select 
  coalesce(org1, org2, org3) as org1,
  case when org1 is not null
    then coalesce(org2, org3) 
    else case when org2 is not null then org3 end
  end as org2
  case when org1 is not null and org2 is not null then org3 end as org3
from mytable;

另一个想法是编写一个存储过程来获取第n个非空值。这不再是纯SQL，我不太了解MySQL是否支持此操作：

select 
  nth_value(1, org1, org2, org3) as org1,
  nth_value(2, org1, org2, org3) as org2,
  nth_value(3, org1, org2, org3) as org3
from mytable;

- Thorsten Kettner

---

谢谢。你的mySQL解决方案几乎可以，但是最后一行重复了“Finance”。 - Artur Kedzior

你说得对。查询语句错误。希望我现在已经修复了它。请检查我的调整后的查询语句。 - Thorsten Kettner