时间戳

首页 > 解决方案 > 如何在 MYSQL 存储过程 WHERE CLAUSE 中添加 IF ELSE IF 以在参数为 NULL 时不在 WHERE 内运行 AND

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

问题描述

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 我不想运行带有 AND 和参数 Where 如果参数为 null 的行。我试过这个

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

但它不起作用。如果参数未给出或为空，我能否获得有关如何制作默认情况的建议。如果我执行先前的解决方法，即对所有 5 种情况进行组合。我必须构建 2^5=64 个案例来检查。有没有更聪明的方法来做到这一点。

标签： mysqlstored-procedures

解决方案

对于问题

不，我想知道只有在参数存在的情况下才能添加 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)

推荐阅读