MYSQL:从其他表插入数据

Question

我正在尝试从另外两个表中插入数据。我目前有两个表，t1 和 t2:

t1:

col1  col2  col3  col4  col5
 A     A     B     A     C
 B     B     C     C     A
 B     C     B     B     A
 C     A     A     A     B

和 t2:

         A   B   C
col1     4   99  81
col2     50  26  38
col3     36  38  11
col4     16  49  70
col5     42  83  93

我的目标是形成一个新表，该表将简单地使用来自 t1 和 t2 的数据并形成一个新表，例如:

col1  col2  col3  col4  col5
 4     50    38    16    93
 99    26    11    70    42
 99    38    38    49    42
 81    50    36    16    83

我一直在使用 CASE 函数，但在附加表格时遇到了问题。有什么建议吗？

谢谢!

Answer 1

如果我们将 t2 从宽表转换为长表:

mysql> CREATE VIEW tidyt2 AS
       SELECT x as 'col', 'A' as 'label', A 'value' FROM t2
       UNION SELECT x as 'col', 'B' as 'label', B 'value' FROM t2
       UNION SELECT x as 'col', 'C' as 'label', C 'value' FROM t2;

Query OK, 0 rows affected (0.03 sec)

mysql> select * from tidyt2;
+------+-------+-------+
| col  | label | value |
+------+-------+-------+
| col1 | A     |     4 |
| col2 | A     |    50 |
| col3 | A     |    36 |
| col4 | A     |    16 |
| col5 | A     |    42 |
| col1 | B     |    99 |
| col2 | B     |    26 |
| col3 | B     |    38 |
| col4 | B     |    49 |
| col5 | B     |    83 |
| col1 | C     |    81 |
| col2 | C     |    38 |
| col3 | C     |    11 |
| col4 | C     |    70 |
| col5 | C     |    93 |
+------+-------+-------+
15 rows in set (0.00 sec)

然后可以使用左连接表示所需的表:

mysql> SELECT t21.value as 'col1'
              , t22.value as 'col2'
              , t23.value as 'col3'
              , t24.value as 'col4'
              , t25.value as 'col5'
         FROM t1 
         LEFT JOIN tidyt2 as t21 ON t1.col1 = t21.label AND t21.col='col1'
         LEFT JOIN tidyt2 as t22 ON t1.col2 = t22.label AND t22.col='col2'
         LEFT JOIN tidyt2 as t23 ON t1.col3 = t23.label AND t23.col='col3'
         LEFT JOIN tidyt2 as t24 ON t1.col4 = t24.label AND t24.col='col4'
         LEFT JOIN tidyt2 as t25 ON t1.col5 = t25.label AND t25.col='col5';

+------+------+------+------+------+
| col1 | col2 | col3 | col4 | col5 |
+------+------+------+------+------+
|    4 |   50 |   38 |   16 |   93 |
|   99 |   26 |   11 |   70 |   42 |
|   99 |   38 |   38 |   49 |   42 |
|   81 |   50 |   36 |   16 |   83 |
+------+------+------+------+------+
4 rows in set (0.00 sec)