Question

Mysql存储过程加个判断条件，急求，好心人帮下忙，谢谢了！

CREATE DEFINER=`root`@`localhost` PROCEDURE `showChildDeptLst`(IN rootId INT,IN status INT)
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpDeptLst
(sno INT PRIMARY KEY AUTO_INCREMENT,id INT,LEVEL INT);
DELETE FROM tmpDeptLst;

CALL createChildDeptLst(rootId,0);

SELECT tmpDeptLst.*,tbl_department.* FROM tmpDeptLst ,tbl_department
where tmpDeptLst.id = tbl_department.ID and PARENT_ID is not null
// while status =1 or 9 do
// and tbl_department.STATUS = status 我想在这里加个判断，怎么加啊
//END WHILE;
ORDER BY tmpDeptLst.sno;

Answer 1

存储过程啊。if status=1 or status=9 then {需要执行的语句} end if;
我想你的意思是判断后再
SELECT tmpDeptLst.*,tbl_department.* FROM tmpDeptLst ,tbl_department
where tmpDeptLst.id = tbl_department.ID and PARENT_ID is not null
后加个and tbl_department.STATUS = status 条件。那么就定义两个varchar变量,第一个比如是A是存
SELECT tmpDeptLst.*,tbl_department.* FROM tmpDeptLst ,tbl_department
where tmpDeptLst.id = tbl_department.ID and PARENT_ID is not null
第二就弄个全局变量或者其他的。
if status=1 or status=9 then
@B=concat(A,'and tbl_department.STATUS = status');

-- 执行sql

prepare create_sql from @B;
EXECUTE create_sql ;
DEALLOCATE prepare create_sql;

end if;

Answer 2

tbl_department.STATUS = IF( status = 1 OR status = 9 , status , tbl_department.STATUS )

或者

tbl_department.STATUS =
CASE WHEN status = 1 OR status = 9 THEN status
ELSE tbl_department.STATUS END

功能就是 如果 status = 1 OR status = 9 才设置查询的条件。