mysql - 在插入语法问题或什么中选择子查询中的变量？

Question

我构造了一个插入，它执行许多操作来计算要插入的值。然而，第一个值只是一个标量，一个不变的 id，它与要计算然后插入的所有记录/字段相关联。该标量值取自一个表，而不是从中提取或计算所有其余值的表。

该值也应该分配给一个变量，我认为应该可以在插入的选择表达式中这样做。我无法做到这一点，我无法弄清楚我哪里出了问题，是否是我的语法错误，或者我是否试图做一些非法的事情。当我在单独的语句中执行此作业时，它有效。正好我把这些语句组合起来，如下:

这很好用(我省略了大部分计算):

select id into @univ_num from u order by id desc limit 1;
insert into um (id_universe, pid, $vol)
   select @univ_num, pid, cv/@univ_len as $vol
     from (select pid as pid, close as close, vol as vol, sum(d.close * d.vol) as cv
       from ... -- from the debug_data_1 table
         where... ) x;

但这并不:

insert into um (id_universe, pid, $vol)
select (select id into @univ_num from u order by id desc limit 1), pid, 
cv/@univ_len as $vol...from..where..etc...

解析器不喜欢“into”:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual...
for the right syntax to use near 'into @univ_num from u order by id desc limit 1), pid, cv/@univ_len as $vol' at line 2

解析器不介意没有“into”；如果没有“into @univ_num”，子查询和查询一起就可以了，即

insert into um (id_universe, pid, $vol)
select (select id from u order by id desc limit 1), pid, cv/@univ_len as $vol

没问题。 TIA

Answer 1

还没有在 MySQL 文档中看到它，但是，这种形式可以使用赋值运算符(第 3 行):

select @univ_num, pid, cv/@univ_len as $vol
 from
   (select  @univ_num := (select id from univ_members order by id desc limit 1) a) b,
   (select pid as pid, close as close, vol as vol, sum(d.close * d.vol) as cv
   from ... -- from the debug_data_1 table
     where... ) x;