MySQL Pivot 查询现有表

Question

我正在尝试创建一个查询，将数据从一个现有表输入到另一个现有表中。两者之间没有公共(public)的 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 用户与 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?

Answer 1

这是您的数据透视表。如果您不想拥有用户 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 >