mysql - 在 temp 中插入存储过程结果时出现错误

Question

我在存储过程中有多个 select 语句，我想将 select 语句的结果插入到临时表中。我已经创建了临时表。我正在使用 mysql 工作台。这是我的一些代码

CREATE DEFINER=`root`@`localhost` PROCEDURE `PNL1`()
BEGIN    

SELECT sum(amount), groupname FROM ( (SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) UNION ALL (SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) UNION ALL (SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) UNION ALL (SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2) UNION ALL (SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) UNION ALL (SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p where groupname='Other Expenses';

insert into temp(amount1,groupname1) values(amount, groupname);

execute PNL1;

select * from temp;

END

我收到此错误

错误代码:1054“字段列表”中的未知列“金额”

Answer 1

试试这个，

insert into temp(amount1,groupname1) 
SELECT sum(amount), groupname FROM ( 
(SELECT -(ramount) AS amount, rgroupname AS groupname FROM receiptsvoucher2) 
 UNION ALL 
(SELECT -(ramount2) AS amount, rgroupname2 AS groupname FROM receiptsvoucher2) 
 UNION ALL 
(SELECT pyamount AS amount,pygroupname AS groupname FROM paymentvoucher2) 
 UNION ALL
(SELECT pyamount2 AS amount, pygroupname2 AS groupname FROM paymentvoucher2)
 UNION ALL 
(SELECT jamount1 AS amount, jgroupname AS groupname FROM journalvoucher2) 
 UNION ALL 
(SELECT -(jamount2) AS amount,jgroupname2 AS groupname FROM journalvoucher2) ) p  
where groupname='Other Expenses';

更多关于Insert-Select