如何在MYSQL存储过程WHERE子句中添加IF ELSE IF以在参数为NULL时不在WHERE内部运行AND

Question

DELIMITER $
CREATE PROCEDURE filing_route(IN appl_name varchar(500),
IN granted char(1),
IN oppose tinyint(4),
IN beginDate date,
IN endDate date,
IN ipc varchar(300))
BEGIN
DROP TABLE IF EXISTS total_company_applications;
DROP TABLE IF EXISTS family_aggregate;
DROP TABLE IF EXISTS tmp_filing_route;
DROP TABLE IF EXISTS tmp_filing_route2;
IF (granted IS NOT NULL AND oppose IS NOT NULL AND beginDate IS NOT NULL AND endDate IS NOT NULL AND ipc IS NOT NULL) THEN
CREATE TEMPORARY TABLE IF NOT EXISTS total_company_applications AS
(SELECT distinct a.appln_id FROM sample.tls201_appln a
INNER JOIN sample.tls207_pers_appln b ON b.appln_id = a.appln_id
INNER JOIN sample.tls906_person c ON c.person_id = b.person_id
INNER JOIN sample.tls209_appln_ipc d ON d.appln_id = a.appln_id
WHERE c.psn_name LIKE CONCAT(appl_name, '%')
AND a.granted LIKE granted
AND a.isOpposed = oppose
AND a.appln_filing_date BETWEEN beginDate and endDate
AND FIND_IN_SET(d.ipc_class_symbol, ipc));
ELSE IF(oppose IS NOT NULL AND beginDate IS NOT NULL AND endDate IS NOT NULL AND ipc IS NOT NULL) THEN
CREATE TEMPORARY TABLE IF NOT EXISTS total_company_applications AS
(SELECT distinct a.appln_id FROM sample.tls201_appln a
INNER JOIN sample.tls207_pers_appln b ON b.appln_id = a.appln_id
INNER JOIN sample.tls906_person c ON c.person_id = b.person_id
INNER JOIN sample.tls209_appln_ipc d ON d.appln_id = a.appln_id
WHERE c.psn_name LIKE CONCAT(appl_name, '%')
AND a.granted LIKE granted
AND a.isOpposed = oppose
AND a.appln_filing_date BETWEEN beginDate and endDate
AND FIND_IN_SET(d.ipc_class_symbol, ipc));
CREATE TEMPORARY TABLE IF NOT EXISTS family_aggregate AS 
(SELECT b.docdb_family_id, b.appln_auth, b.receiving_office, MIN(b.appln_filing_date) as first_date 
FROM total_company_applications a
INNER JOIN tls201_appln b ON b.appln_id = a.appln_id 
WHERE b.appln_filing_year < 9999
GROUP BY b.docdb_family_id, b.appln_auth, b.receiving_office
ORDER BY b.docdb_family_id, first_date);
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_filing_route AS (
SELECT docdb_family_id, first_date, group_concat(distinct appln_auth, IF(appln_auth = 'WO', CONCAT(' (RO = ', receiving_office, ')'), '') ORDER BY appln_auth DESC separator ', ') as filing_route 
FROM family_aggregate
GROUP BY docdb_family_id, first_date);
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_filing_route2 AS (
SELECT docdb_family_id, group_concat(filing_route ORDER BY first_date separator ' -> ') as filing_route
FROM tmp_filing_route
GROUP BY docdb_family_id);
SELECT filing_route, COUNT(distinct docdb_family_id) as num_families
FROM tmp_filing_route2
GROUP BY filing_route
ORDER BY num_families DESC
LIMIT 20;

END$

我有一个存储过程filing_route，如果参数为null，我不想运行带有AND和参数Where的行。我试过这个

WHERE c.psn_name LIKE CONCAT(appl_name, '%')
IF(granted IS NOT NULL)
AND a.granted LIKE granted
END IF
AND a.isOpposed = oppose

但它不起作用。如果参数没有给定或为null，我可以得到如何设置默认情况的建议吗。如果我做前面的解决方法，那就是对所有5种情况进行组合。我必须建立2^5=64个案例来检查。有更聪明的方法吗。

Answer 1

对于问题

不，我想知道只有当参数存在时，我才能添加AND

您可以尝试以下操作，只需使用IF()，关键是当条件不匹配时，只需添加1=1即可使sql语法正确

WHERE c.psn_name LIKE CONCAT(appl_name, '%')
AND a.isOpposed = oppose
AND IF(granted IS NOT NULL,a.granted LIKE granted,1=1)