mysql - 从具有多个自定义列的现有表创建新表

Question

我一直在寻找这个问题的答案，但我什么也没找到。

目前这是我当前的查询

  create TABLE VAR_PEAK
    as SELECT a.ID_NO, a.CODE,
    a.VAR1 as PKEI_1,
    a.VAR2 as PKEI_2,
    a.VAR3 as PKEI_3,
    b.VAR1 as PKE_1,
    b.VAR2 as PKE_2,
    b.VAR3 as PKE_3,
    c.VAR1 as PKW_1,
    c.VAR2 as PKW_2,
    c.VAR3 as PKW_3,
    d.VAR4 as PKWE_1,
    d.VAR4 as PKWE_2,
    d.VAR4 as PKWE_3
    from LIVE_VARAN a INNER JOIN LIVE_VARAN b ON a.ID_NO=b.ID_NO 
    INNER JOIN LIVE_VARAN c ON a.ID_NO=c.ID_NO
    INNER JOIN LIVE_VARAN d ON a.ID_NO=d.ID_NO

    WHERE a.VARIABLE = 'PKEI' AND a.POS = 'DES' AND b.VARIABLE = 'PKE' AND b.POS = 'DES' 
    AND c.VARIABLE = 'PKW' AND c.POS = 'DES' AND d.VARIABLE = 'PKWE' AND d.POS = 'DES'

当前的查询只是不运行，它只是加载并永远加载。

数据是这样的

ID_NO  VARIABLE  POS   VAR1   VAR2  VAR3
D55     PKEI     MES   4      5     9
D24     PKEI     MES   4      5     5
D78     PKE      MES   4      3     9
D45     PKE      MES   4      5     9
D54     PKWE     MES   21     5     9
D45     PKWE     MES   4      54    9
D55     PKW      MES   9      8     4
D54     PKEI     MES   1      1     4
D55     INT      CLE   100    100   124

我尝试创建的新表将具有如下所示的新列标题

ID_NO  CODE  PKEI_1 PKEI_2 PKEI_3 PKE_1 PKE_2 PKE_3 PKW_1 PKW_2 PKW_3 PKWE_1 PKWE_2 PKWE_3

任何人都可以发现我在查询中做错了什么吗？

Answer 1

您的加入不正确:

您正在使用相同的 ID 加入:a.ID_NO=b.ID_NO , a.ID_NO=c.ID_NO , a.ID_NO=d.ID_NO和
只有一个VARIABLE每个记录的字段，但您尝试连接 a、b、c、d 中的行，其中 a.VARIABLE = 'PKEI'和 b.VARIABLE = 'PKE'和 c.VARIABLE = 'PKW'和 d.VARIABLE = 'PKWE' 。怎么可能？

您需要更改=至<>

a.ID_NO <> b.ID_NO , a.ID_NO <> c.ID_NO , a.ID_NO <> d.ID_NO

修改后的SQL

CREATE TABLE VAR_PEAK AS
SELECT a.ID_NO,
       a.CODE,
       a.VAR1 AS PKEI_1,
       a.VAR2 AS PKEI_2,
       a.VAR3 AS PKEI_3,
       b.VAR1 AS PKE_1,
       b.VAR2 AS PKE_2,
       b.VAR3 AS PKE_3,
       c.VAR1 AS PKW_1,
       c.VAR2 AS PKW_2,
       c.VAR3 AS PKW_3,
       d.VAR4 AS PKWE_1,
       d.VAR4 AS PKWE_2,
       d.VAR4 AS PKWE_3
FROM LIVE_VARAN a
INNER JOIN LIVE_VARAN b ON a.ID_NO<>b.ID_NO
INNER JOIN LIVE_VARAN c ON a.ID_NO<>c.ID_NO
INNER JOIN LIVE_VARAN d ON a.ID_NO<>d.ID_NO
WHERE a.VARIABLE = 'PKEI'
  AND a.POS = 'DES'
  AND b.VARIABLE = 'PKE'
  AND b.POS = 'DES'
  AND c.VARIABLE = 'PKW'
  AND c.POS = 'DES'
  AND d.VARIABLE = 'PKWE'
  AND d.POS = 'DES';