mysql - 在子查询中使用in参数？

Question

可以在子查询中使用in参数吗？

我尝试连接三个表，并在子查询中使用参数，但出现错误:

drop procedure if exists displayFilmInfo;
delimiter //
create procedure displayFilmInfo(in in_category_id tinyint, in in_language_id tinyint)
begin

    if in_category_id != 0  and in_language_id != 0 then
        select film.title, after_category.name from film
            inner join film_category on film_category.film_id = film.film_id
            inner join (
                select category_id, name from category when category.category_id =  in_category_id
            ) after_category on after_category.category_id = film_category.category_id
            inner join (
                select language_id, name from language when in_language_id = language.language_id
            ) after_language on after_language.language_id = film.language_id
    end if;

end //
delimiter ;

call displayFilmInfo();

它显示:

Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'when category.category_id = in_category_id ) after_category on afte' at line 8 ERROR 1305 (42000): PROCEDURE sakila.displayFilmInfo does not exist

Answer 1

请将您的选择语句更新为 -

select film.title,
       after_category.name
from film
inner join film_category on film_category.film_id = film.film_id
inner join (select category_id,
                   name
            from category
            where category.category_id =  in_category_id) after_category on after_category.category_id = film_category.category_id
inner join (select language_id,
                   name
            from language
            where in_language_id = language.language_id) after_language on after_language.language_id = film.language_id