php - 从先前生成的结果集中获取值(value)

Question

我有一个存储过程，在其中运行查询两次以获得所需的输出，如下所示

CREATE PROCEDURE `GetSeries`(IN id INT)
BEGIN
   select pid from                      //<----This Query is executed twice
       (select pid from poststatus 
           where uid = id and pid > 10 
           order by pid desc limit 50
       ) T

UNION

select pid from postshare where uid = id and pid 
between 10 and               
       (select pid from                //<----Running The previous query again
       (select pid from poststatus          
           where uid = id and pid > 10 
           order by pid desc limit 50
       ) T limit 1);             
END

我希望查询执行一次，然后从该查询中获取值。我怎样才能这样做呢？

Answer 1

您可以将第一个查询的输出存储到临时表中。
将列表中的第一个 pid 读取到局部变量中，并在第二个查询中使用相同的变量。

UNION 临时表记录第二个查询以获得所需结果。

示例:

CREATE PROCEDURE `GetSeries`( IN id INT )
BEGIN
  declare _pid int;

  create temporary table pid_list_50 as 
    select pid from poststatus 
     where uid = id and pid > 10 
     order by pid desc 
     limit 50;

  select pid into _pid from pid_list_50 limit 1;

  select * from pid_list_50
  union
  select pid from postshare 
   where uid = id and pid between 10 and _pid;

  DROP TEMPORARY TABLE pid_list_50;
END