MySql 存储过程组合

Question

Si 我有这个 MySql 存储过程，用于 gridview 过滤器。对于 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

Answer 1

检查参数是否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);