MySQL:WHERE 子句中的存储过程

Question

所以我想将 param 的动态值从父查询传递给存储过程，但是它会产生错误，这里是查询:

SELECT *
FROM mytable
WHERE mytable.user_id IN (CALL getDownlines(mytable.user_id))

但是当我直接运行 SP 时，它工作正常:

CALL getDownlines(100)

有什么想法吗？

这里有SP供引用，不知道怎么转成函数:

DELIMITER $

DROP PROCEDURE IF EXISTS getDownlines$

CREATE PROCEDURE getDownlines(in_id INT)
BEGIN

    drop table if exists temp1;
    drop table if exists temp2;
    drop table if exists results; 

    create temporary table temp2 as (select id, upline_id from agents where upline_id = in_id); 
    create temporary table results as (select id, upline_id from temp2); 
    create temporary table temp1 (id int, upline_id int);

    while (select count(*) from temp2) do 

        insert into temp1 (id, upline_id)
        select a.id, upline_id 
        from agents a
        where a.upline_id in (select id from temp2) ;

        insert into results (id, upline_id)
        select distinct id, upline_id
        from temp1;

        delete from temp2;

        insert into temp2 (id, upline_id)
        select distinct id, upline_id
        from temp1;

        delete from temp1;
    end while;    

    select a.* 
    from results r
    join agents a
    on a.id = r.id;

    drop table if exists temp1;
    drop table if exists temp2;
    drop table if exists results; 

End $$  

DELIMITER ;  

Answer 1

从 select 调用过程是不可能的。分两步完成:

1. 调用过程并将结果存储在某处。
2. 针对过程的结果运行选择。

或者将您的过程变成一个函数。