主页 / dba / 问题 / 274232
Accepted
Kokizzu
Asked: 2020-08-25 02:37:47 +0800 CST

我们可以在 MySQL 中使用 x AS ( ... ) INSERT INTO y(k) SELECT * FROM x 吗？

  • 772

我们可以做INSERT INTO SELECT，但是如果我有一堆WITH AS陈述（大约 27ish）SELECT呢？我目前的解决方案是进行查询，然后使用编程语言进行扫描（Golang就我而言），然后使用普通INSERT INTO语句一一插入。有没有一步解决方案来做到这一点？

mysql select

2 个回答

  1. Best Answer

    就像是

    insert into item
  with x as (    
    select id + 10 as id1, concat(brand,' D') as brand1 from item),
       y as (    
    select id + 20 as id1, concat(brand,' E') as brand1 from item)  
  select * from x   
  union all   
  select * from y

    我们可以！

    • 2

  2. Ir 在一个级别上不允许有多个 WITH

    你只能使用子查询

    INSERT INTO testable WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

    但是您可以在不同级别使用 With 子句，例如

    INSERT INTO testtabe WITH cte1 AS (SELECT 1)
SELECT * FROM (WITH cte2 AS (SELECT 2) SELECT * FROM cte2 JOIN cte1) AS dt;
    • 2

相关问题