mysql - 将表1中所有2等于数据的记录复制到表2中的一条记录

Question

早上好，
我在 mysql 中遇到这个问题。
我有一张这样的 table :

表1

AccessDate__UserCode__AnalisisName____AnalisisResult

2012-01-01____1____________a1______________10  
2012-01-01____1____________a2______________15  
2012-01-01____1____________a3______________12  

2014-03-01____1____________a1______________12  
2014-03-01____1____________a3______________13  

2012-01-01____2____________a1______________14  
2012-01-01____2____________a2______________15  
2012-01-01____2____________a3______________20  

2013-03-01____2____________a1______________3  

2012-01-01____3____________a1______________2  
2012-01-01____3____________a2______________1  

2014-01-01____3____________a1______________10  

我想复制另一个表中的数据，在同一记录上设置具有相同 AccessDate 和 UserCode 的所有记录，并使用 AnalisisName 值作为列指针。
我的意思是:

表2

AccessDate___UserCode___a1__a2__a3  

2012-01-01_____1________10__15_12  
2014-03-01_____1________12___-_13  
2012-01-01_____2________14__15_20  
2013-03-01_____2_________3___-_-  
2012-01-01_____3_________2___1_-  
2014-01-01_____3________10___-_-  

非常感谢您的帮助!!
最好的问候
保罗

Answer 1

创建新表:

CREATE TABLE newtable
(AccessDate date NOT NULL,
UserCode integer NOT NULL,
a1 integer,
a2 integer,
a3 integer,
CONSTRAINT newtable_pk PRIMARY KEY (AccessDate)
);

插入a1记录:

INSERT INTO newtable
(AccessDate, UserCode, a1)
SELECT AccessDate, UserCode, AnalisisResult
FROM table1
WHERE AnalisisName='a1';

插入a2记录:

INSERT INTO newtable
(AccessDate, UserCode, a2)
SELECT AccessDate, UserCode, AnalisisResult
FROM table1
WHERE AnalisisName='a2';

插入a3记录:

INSERT INTO newtable
(AccessDate, UserCode, a3)
SELECT AccessDate, UserCode, AnalisisResult
FROM table1
WHERE AnalisisName='a3';