MySql 存储过程组合

debugcn 发表于 Dev

普卡什

Si 我有这个用于 gridview 过滤器的 MySql 存储过程。对于 3 列，我必须编写这么多查询组合。我必须在 7 列上使用过滤器，这将产生 7x7x7x7x7x7x7 数字组合。有没有更好的方法来实现这一目标？

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetApprovedData`
(in siteValue varchar(45),
 in skillValue varchar(100), in shiftValue varchar(100))
 BEGIN

 IF siteValue IS NULL and skillValue IS NULL and shiftValue IS NULL THEN 
  select * from approved;

 ELSEIF siteValue IS NULL and skillValue IS NULL and shiftValue IS NOT NULL    THEN
  select * from approved where shift = shiftValue;

 ELSEIF siteValue IS NULL and skillValue IS NOT NULL and shiftValue IS NULL THEN
  select * from approved where skill = skillValue;

 ELSEIF siteValue IS NOT NULL and skillValue IS NULL and shiftValue IS NULL THEN
  select * from approved where site = siteValue;

 ELSEIF siteValue IS NULL and skillValue IS NOT NULL and shiftValue IS NOT NULL THEN
  select * from approved where skill = skillValue and shift = shiftValue;

ELSEIF siteValue IS NOT NULL and skillValue IS NOT NULL and shiftValue IS NULL THEN
  select * from approved where site = siteValue and skill = skillValue;

ELSEIF siteValue IS NOT NULL and skillValue IS NULL and shiftValue IS NOT NULL THEN
  select * from approved where site = siteValue and shift = shiftValue;

ELSE        
  select * from approved where site = siteValue and skill = skillValue and shift = shiftValue;
END IF;

END

粘点

检查参数IS NULL或行的列值是否与参数值匹配。

SELECT *
       FROM approved
       WHERE (sitevalue IS NULL
               OR site = sitevalue)
             AND (skillvalue IS NULL
                   OR skill = skillvalue)
             AND (shiftvalue IS NULL
                   OR shift = shiftvalue);

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。