mysql - mysql存储过程中的多个select语句

Question

我正在学习编写存储过程。我想要对我的 mysql 存储过程使用三个 select 语句。第二个和第三个 select 语句需要它们上面的 select 语句的结果。我尝试写如下。但没有成功。

CREATE DEFINER=`root`@`localhost` PROCEDURE `SPTeam`(IN date_x date, OUT   emplist3 varchar(200))

BEGIN
SELECT
(
   select emp_event.employee_id as emplist1
   from emp_event
   where emp_event.date = date_x
), 
(
   select emp_position.employee_id as emplist2
   from emp_position
   where FIND_IN_SET(emp_position.employee_id, emplist1)=0
   AND emp_position.position = "Cameraman"
),
(
   select employee.name as emplist3
   from employee
   where employee.id IN (emplist2)
);
END

这是我遇到的错误。

Error Code: 1054. Unknown column 'emplist1' in 'where clause'   

有人请纠正我。谢谢你。

Answer 1

您可以将每个选择结果存储在变量中，然后对这些变量进行选择:

DECLARE @emp_event as int
DECLARE @emp_position as int
DECLARE @emp_employeeName as int

SET @emp_event = (select emp_event.employee_id from emp_event where emp_event.date = date_x)
SET @emp_position = (select emp_position.employee_id from emp_position where FIND_IN_SET(emp_position.employee_id, @emp_event)=0 AND emp_position.position = "Cameraman")
SET @emp_employeeName = (select employee.name from employee where employee.id IN (@emp_position))

SELECT @emp_event as emplist1, @emp_position as emplist2, @emp_employeeName as emplist3