mysql - MySQL Pivot查询到现有表

我正在尝试创建一个查询，该查询会将一个现有表中的数据输入到另一个现有表中。两者之间没有共同的ID字段。

我有以下现有表t1

    ----------+------+-------+
    |user | criteria | record|
    ----------+------+-------+
    | 1    | 11      | K     |
    ----------+------+-------+
    | 1    | 12      | L     |
    ----------+------+-------+
    | 1    | 13      | M     |
    ----------+------+-------+
    | 1    | 16      | P     |
    ----------+------+-------+
    | 1    | 18      | R     |
    ----------+------+-------+
    | 1    | 20      | T     |
    ----------+------+-------+
    | 2    | 11      | K     |
    ----------+------+-------+
    | 2    | 12      | L     |
    ----------+------+-------+
    | 2    | 13      | M     |
    ----------+------+-------+
    | 2    | 16      | P     |
    ----------+------+-------+
    | 2    | 18      | R     |
    ----------+------+-------+
    | 2    | 20      | T     |
    ----------+------+-------+

用户数量众多，（约有100个标准）有6个标准
我有兴趣插入以下现有表格中

 table t2

 +----------+----------+----------+----------+----------+----------+
 | Label u  | Label v  | Label w  | Label x  | Label y  | Label z |
 +----------+----------+----------+----------+----------+----------+
 | record K | record L | record M | record P | record R  | record T|
 | record K | record L | record M | record P | record R  | record T|
 +----------+----------+----------+----------+----------+----------+

 where Criteria number 11 = Label u
       Criteria number 12 = Label v
       Criteria number 13 = Label w
       Criteria number 16 = Label x
       Criteria number 18 = Label y
       Criteria number 20 = Label z

 Note* Line 1 in t2 corresponds to user 1
       Line 2 in t2 corresponds to user 2

       There is no "user" column in t2 for the fields "user 1, user 2"

       t2 already contains data in other columns

t1用户与user中t2中的列相同。从t1插入的t1用户的数据必须与t2已经存在的同一用户的数据匹配。

我的最终查询是这样的（不起作用）

INSERT INTO t2 a
  SELECT
  GROUP_CONCAT(IF (criteria = 11,record,NULL)) AS Label_u,
  GROUP_CONCAT(IF (criteria = 12,record,NULL)) AS Label_v,
  GROUP_CONCAT(IF (criteria = 13,record,NULL)) AS Label_w,
  GROUP_CONCAT(IF (criteria = 14,record,NULL)) AS Label_x,
  GROUP_CONCAT(IF (criteria = 15,record,NULL)) AS Label_y,
  GROUP_CONCAT(IF (criteria = 16,record,NULL)) AS Label_z
  FROM mytable b
  WHERE a .user field = b.user
  GROUP BY USER
  ORDER BY USER;;

       I have tried many - Can someone help me find a functional query to do this?

最佳答案

这是您的数据透视表。如果您不想拥有用户ID，则可以删除2.行

SELECT
  user,
  GROUP_CONCAT(IF (criteria = 11,record,NULL)) AS Label_u,
  GROUP_CONCAT(IF (criteria = 12,record,NULL)) AS Label_v,
  GROUP_CONCAT(IF (criteria = 13,record,NULL)) AS Label_w,
  GROUP_CONCAT(IF (criteria = 14,record,NULL)) AS Label_x,
  GROUP_CONCAT(IF (criteria = 15,record,NULL)) AS Label_y,
  GROUP_CONCAT(IF (criteria = 16,record,NULL)) AS Label_z
FROM mytable
WHERE user IN (1,2)
GROUP BY USER
ORDER BY USER;

结果

+------+---------+---------+---------+---------+---------+---------+
| user | Label_u | Label_v | Label_w | Label_x | Label_y | Label_z |
+------+---------+---------+---------+---------+---------+---------+
|    1 | P1      | P1      | P1      | P1      | R1      | T1      |
|    2 | P2      | P2      | P2      | P2      | R2      | T2      |
+------+---------+---------+---------+---------+---------+---------+
2 rows in set (0.00 sec)

MariaDB >

关于mysql - MySQL Pivot查询到现有表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33266805/