mysql - 根据 MySql 中的两个条件在单列中获取计数的更好方法

Question

我正在尝试为以下问题陈述编写查询。我想写一个查询，在那里我可以找到有效和无效的签证。目前我的表里有两种签证类型

1. 商务签证
2. 工作许可

我有一个主表tbl_employee_visa_request，我在其中存储了员工提出的所有请求。

我有两个表(tbl_visa_request_status 和 tbl_uscis)，我必须在其中检查任何签证是否有效。

tbl_visa_request_status 的结构是

id | requestId| visaStartDate|visaEndDate
-----------------------------------------
 1 | 123| 01-01-2017|31-03-2017
 2 | 567| 01-01-2017|19-12-2018
 3 | 45456| 01-01-2017|01-01-2016
 4 | 56756| 01-01-2017|17-03-2017
 5 | 343| 01-01-2017|01-01-2019

tbl_uscis

 id | requestId| caseNumber|isActive
    -----------------------------------------
     1 | 123| A435|1
     2 | 567| 1BCFG324|0
     3 | 45456| 234R|0
     4 | 56756| AB456|1
     5 | 343| AKO45|1

如果我必须检查工作许可证 签证的有效性，那么我必须检查tbl_uscis 中的isActive 字段。如果为 0 则无效，如果为 1 则有效。

如果我想检查商务签证的有效性，那么我必须比较tbl_visa_request_status 中的 visaEndDate 为当前日期。如果 EndDate 大于当前日期则有效，否则无效。

我已经编写了一个查询来通过使用两个查询的联合来实现此功能，如下所示:

SELECT vt.`visa_type` AS visaType
        ,COUNT(CASE WHEN vrs.`visa_end_date` > NOW() THEN 1 END) AS Valid
        ,COUNT(CASE WHEN vrs.`visa_end_date` < NOW() THEN 1 END) AS InValid
        FROM `tbl_visa_request_status` vrs
        LEFT JOIN `tbl_employee_visa_request` evr 
        ON evr.id = vrs.`tbl_employee_visa_request_id`
        LEFT JOIN `tbl_visa_type` vt
        ON evr.`tbl_visa_type_id` = vt.`id`
        WHERE evr.`tbl_visa_type_id` != 2
        GROUP BY evr.`tbl_visa_type_id`

        UNION

        SELECT vt.`visa_type` AS visaType
        ,COUNT(CASE WHEN u.`is_active` = 1 THEN 1 END) AS Valid
        ,COUNT(CASE WHEN u.`is_active` = 0 THEN 1 END) AS InValid
        FROM `tbl_uscis` u
        LEFT JOIN `tbl_employee_visa_request` evr 
        ON evr.id = u.`tbl_employee_visa_request_id`
        LEFT JOIN `tbl_visa_type` vt
        ON evr.`tbl_visa_type_id` = vt.`id`
        GROUP BY evr.`tbl_visa_type_id`;

我得到如图所示的输出。

但我的问题是，当有新的签证类型时，我必须检查不同的有效性条件，然后我将不得不编写一个与当前查询联合的新查询。

我想知道的是，是否有更好的方法来实现此功能，而不是编写一个全新的查询，我可以简单地更改单个查询的 where 子句 中的一些条件当我的数据库中添加新的签证类型时。

Answer 1

您必须将所有类型签证的有效性标准存储在一个表中，以便在添加新签证类型时不必重写查询。

只要不同类型的详细信息存储在不同的查询中，每次添加新签证类型时都必须更改查询。您可以使用联接而不是联合，但新的签证类型仍然意味着新的联接。

我会创建一个单独的 visas 表来保存所有签证的所有通用数据，其中包括有效期开始日期和结束日期(我从未见过没有这 2 个日期的签证)。您可以使用这些数据编写单个查询，如果您添加新的签证类型，甚至不必修改该查询。