MySQL insert语句（插入表（列）select语句）

Question

我试图将值插入到表的选定列中，但只插入 MySQL 语句的结果。 select 语句本身可以正常工作并返回结果。

当与插​​入语句结合使用时，它失败了，

error incorrect syntax near `dedspgoods`.`case number`.

谁能帮助我使用正确的语法？我的错误语法如下：

insert into despgoods_alldetails 
  (`case number`, weight, customer, transporttypename) 
values 
( select despgoods.`case number`
       , despgoods.weight
       , despgoods.customer
       , customers.transporttypename 
  from despgoods 
    inner join customers 
      on despgoods.customer = customers.customer )

Answer 1

如果这是有效的 SELECT：

select despgoods.`case number`
     , despgoods.weight
     , despgoods.customer
     , customers.transporttypename 
from despgoods 
  inner join customers 
    on despgoods.customer = customers.customer

那么请尝试此操作（请注意，如果您想要插入 SELECT 的结果集，则没有 VALUES 子句:

insert into despgoods_alldetails 
  (`case number`, weight, customer, transporttypename) 
select despgoods.`case number`
     , despgoods.weight
     , despgoods.customer
     , customers.transporttypename 
from despgoods 
  inner join customers 
    on despgoods.customer = customers.customer

Answer 2

正如@EdHeal所述，您需要将case number括在引号或反引号之间，因为列名称中有空格（事实上CASE是保留字实际上只是一个有趣的细节，但这并不是在这种情况下破坏查询的原因）。

insert into despgoods_alldetails (`case number`,weight,customer,transporttypename) 
values (
    select despgoods.`case number`, despgoods.weight, despgoods.customer, customers.transporttypename
    from despgoods 
    inner join customers on (despgoods.customer=customers.customer)
)

Answer 3

case 是保留字。需要将“案件编号”放在引号中。

Answer 4

这里它去

 INSERT INTO despgoods_alldetails(
                                  casenumber,
                                  weight,
                                  customer,
                                  transporttyplename)


 VALUES(SELECT desp.casenumber,
        desp.weight,
        despgoods.customer, 
        customers.transporttypename)

 FROM despgoods
 INNER JOIN customers on despgoods.customer = customers.customer

那里。那应该可以正常工作。请记住，不要用空格命名变量，因为这可能会变得非常棘手。我认为你的 case 编号有误，因为当 case 是 MySQL 中的函数时，它之间有一个空格。如果您还需要什么，请告诉我。