MySQL：使用来自查询的信息创建一个新表

发布于07月06日

在MySQL中，我想用这个查询中的所有信息创建一个新表:

select * into consultaa2 from SELECT
 CONCAT(    'UPDATE customers SET
 customers_default_address_id= ',    
 (SELECT a.address_book_id FROM
 address_book a where
 c.customers_id=a.customers_id order by
 address_book_id desc limit 1),    '
 WHERE customers_id = ', customers_id,
 ';') AS sql_statement FROM customers c
 where c.customers_id > 3894;

查询太长，浏览器无法显示concat，我需要它来进行更新.

推荐答案

*注意this method does not create a table(根据作品标题).要做到这一点，请参见this answer*

将查询中的信息插入表的格式如下

INSERT INTO <TABLE-1> 
SELECT * FROM <TABLE-2>

在你的情况下，应该是

insert into consultaa2 
SELECT CONCAT( 'UPDATE customers SET customers_default_address_id= ',
(SELECT a.address_book_id FROM address_book a where c.customers_id=a.customers_id order by address_book_id desc limit 1), ' WHERE customers_id = ', customers_id, ';') AS sql_statement FROM customers c where c.customers_id > 3894;

只需确保要插入的表中的列与select查询返回的列匹配即可.