如何编写SQL查询主协议禁用但子协议处于激活状态的记录

数据表规则 #

• CustStatus字段：1代表active（激活），0代表disabled（禁用）
• 主客户协议编号为5位数字
• 子协议编号为10位，前5位与所属主协议编号一致，后5位为自动生成，并非所有主协议都存在对应的子协议

目标需求 #

筛选所有子协议状态为active、但其对应的主协议状态为disabled的行记录。

样例数据 #

+-------------+----------+------------+--+
|   CustNumber| CustName | CustStatus |  |
+-------------+----------+------------+--+
|12345        | Cust1    |          1 |  |
|1234500001   | Cust1    |          1 |  |
|1234500002   | Cust1    |          0 |  |
|12346        | Cust2    |          0 |  |<---
|1234600001   | Cust2    |          1 |  |<---
|1234600002   | Cust2    |          0 |  |
+-------------+----------+------------+--+

实现SQL #

用自连接即可实现需求，写法如下：

SELECT 
    t1.CustNumber AS child_custnumber,
    t1.CustName AS custname,
    t1.CustStatus AS child_custstatus,
    t2.CustNumber AS parent_custnumber,
    t2.CustStatus AS parent_custstatus
FROM `table` t1
-- 关联对应主协议
INNER JOIN `table` t2 
    ON LEFT(t1.CustNumber,5) = t2.CustNumber
    -- 限定t1是子协议（长度10）、t2是主协议（长度5），避免错误关联
    AND LENGTH(t1.CustNumber) = 10
    AND LENGTH(t2.CustNumber) = 5
-- 过滤条件：子协议激活、主协议禁用
WHERE t1.CustStatus = 1 
AND t2.CustStatus = 0
ORDER BY t1.CustNumber;

逻辑说明 #

• 给同一张表起两个别名t1、t2，分别代表子协议数据集和主协议数据集
• 关联条件保证子协议和对应主协议精准匹配
• 过滤条件直接命中需求，针对你给出的样例数据，这条语句会返回1234600001的子协议记录，符合预期。

内容的提问来源于stack exchange，提问作者LambSauce