mysql - reshape 表，使日期在 MySQL 的列中是连续的

Question

我有一个像这样的简单表格:

| ID | Time1 | Time2 | Time3 |
|----|-------|-------|-------|
| 1  | 2005  |       |       |
| 2  | 2003  | 2004  | 2005  |
| 3  | 2004  |       |       |
| 4  | 2003  | 2005  |       |
| 5  | 2005  |       |       |

有没有一种比较轻松的方法来 reshape 这张 table 并最终得到这样的 table ？

正如您所看到的，我想排列 3 个 TimeN 列中格式不良的值，以便它们按顺序排序，并且在日期不存在的地方存在空值。

| ID | Time1 | Time2 | Time3 |
|----|-------|-------|-------|
| 1  |       |       | 2005  |
| 2  | 2003  | 2004  | 2005  |
| 3  |       | 2004  |       |
| 4  | 2003  |       | 2005  |
| 5  |       |       | 2005  |  

我认为一定有比我目前扩展的 50 行可怕的 MySQL 更简单的方法。

Answer 1

没有什么神秘的 SQL 语句可以做到这一点。

您将需要一个 SQL 语句来为每个列分配值，如下所示:

 UPDATE mysimpletable t
    SET t.Time1 = expr
      , t.Time2 = expr
      , t.Time3 = expr

我建议您首先构建一个新表。获取返回您想要的结果的 SELECT 语句。

如果您可以确定每列中应包含哪个值，这将是最简单的。如果 2003 是最低值，并且您希望该值出现在 Time1 列中(如果该值出现在任何 TimeN 列中)，每个值都固定到特定列，如下所示:

 SELECT s.id
      , IF('2003' IN (s.Time1,s.Time2,s.Time3),'2003',NULL) AS Time1
      , IF('2004' IN (s.Time1,s.Time2,s.Time3),'2004',NULL) AS Time2
      , IF('2005' IN (s.Time1,s.Time2,s.Time3),'2005',NULL) AS Time3
   FROM mysimpletable s
  ORDER BY s.id

如果返回您想要的结果，假设 id 列是非空唯一键，​​我会这样做:

 CREATE TABLE newtable (PRIMARY KEY (id)) AS SELECT ...

一旦我确定这是正确的，只有他们我才会执行更新。

 UPDATE newtable s
   JOIN mysimpletable t
     ON t.id = s.id
    SET t.Time1 = s.Time1
      , t.Time2 = s.Time2
      , t.Time3 = s.Time3

此方法应该适用于示例数据。但同样，这假设我们知道 2003 将是存储在 Time1 中的值，2004 是存储在 Time2 中的值，依此类推。

可能有一种更简单、更简单的方法。但这是我要采取的方法...我首先使用 SELECT 语句来处理它，开发返回每列值的表达式。然后从该 SELECT 创建一个临时工作表。然后在确定这就是我想要的之后，我将使用工作表作为源进行更新。

但我也会质疑为什么该表是这样设计的，从而要求首先执行此类更新。