MariaDB查询：找出距可用钥匙仅差1把锁的挂锁

背景与需求 #

现有三张表：挂锁表paddocks、锁表locks、钥匙临时表ky。当前查询仅能返回精确匹配的挂锁（即挂锁所需的每一种锁，钥匙的数量都满足需求），比如返回A、B、C，但遗漏了仅差1种锁（RO）的挂锁D。需要实现两种可切换的查询模式：

• 模式1：仅返回精确匹配的挂锁
• 模式2：返回精确匹配 + 仅差1种锁的挂锁

表结构与测试数据 #

挂锁表 paddocks #

create table paddocks (
pd char(1),
sz enum('Small', 'Big'),
key (sz));

插入测试数据：

insert into paddocks values
('A', 'Small'),
('B', 'Small'),
('C', 'Small'),
('D', 'Small'),
('E', 'Small'),
('F', 'Big');

锁表 locks #

create table locks (
pd char(1),
lk char(2),
key (pd,lk));

插入测试数据：

insert into locks values ('A', 'KL'),('A', 'OK'),('A', 'CZ'),('A', 'CZ'),
('B', 'OK'),('B', 'OK'),
('C', 'OK'),('C', 'CZ'), ('C', 'KL'),
('D', 'RO'),('D', 'CZ'), ('D', 'CZ'),
('E', 'OK'),('E', 'OK'), ('E', 'KL'), ('E', 'KL'),
('F', 'OK'),('F', 'OK'), ('F', 'CZ'), ('F', 'KL');

钥匙临时表 ky #

create temporary table ky (
lk char(2),
key (lk));

插入测试数据：

insert into ky values
('KL'),('OK'),('CZ'),('OK'),('CZ');

解决方案 #

核心思路是统计每个挂锁的未满足锁的种类数，通过修改筛选条件实现模式切换：

模式1：仅返回精确匹配的挂锁 #

SELECT pd
FROM (
    SELECT 
        pd,
        p.lk,
        p.c,
        COUNT(ky.lk) AS kc
    FROM (
        SELECT 
            pd,
            locks.lk,
            COUNT(locks.lk) AS c
        FROM paddocks
        JOIN locks USING (pd)
        WHERE sz = 'Small'
        GROUP BY pd, locks.lk
    ) p
    LEFT JOIN ky USING (lk)
    GROUP BY pd, p.lk
) p_stats
GROUP BY pd
HAVING SUM(CASE WHEN c > kc THEN 1 ELSE 0 END) = 0;

返回结果：A, B, C

模式2：返回精确匹配 + 仅差1种锁的挂锁 #

只需修改HAVING子句的条件，将未满足的锁种类数放宽到≤1：

SELECT pd
FROM (
    SELECT 
        pd,
        p.lk,
        p.c,
        COUNT(ky.lk) AS kc
    FROM (
        SELECT 
            pd,
            locks.lk,
            COUNT(locks.lk) AS c
        FROM paddocks
        JOIN locks USING (pd)
        WHERE sz = 'Small'
        GROUP BY pd, locks.lk
    ) p
    LEFT JOIN ky USING (lk)
    GROUP BY pd, p.lk
) p_stats
GROUP BY pd
HAVING SUM(CASE WHEN c > kc THEN 1 ELSE 0 END) <= 1;

返回结果：A, B, C, D

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