Sql MariaDB查询在逗号分隔的字符串中查找多个值

通过将锁规范化到新表是非常简单的:

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

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

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

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');

然后为您拥有的密钥创建一个临时表:

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

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

这构成了中间查询的一部分，以计算出每个围场每次有多少锁.

select pd, locks.lk, count(locks.lk)
from paddocks
join locks using (pd)
where sz = 'Small'
group by pd, locks.lk

将此与我们拥有的每种类型的密钥数量进行比较:

SELECT pd,
       p.lk,
       p.c,
       count(ky.lk) AS kc
FROM
  (SELECT pd,
          locks.lk AS 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,
         lk

现在再次按pd分组，并 Select 那些所有锁的 keys 比锁多的锁.count(pd)是锁的数量.如果没有足够的密钥，则c <= kc是布尔0，如果有足够的密钥，则为1.对于所有类型的锁，都应该有足够的 keys .

SELECT pd
FROM
  (SELECT pd,
          p.lk,
          p.c,
          count(ky.lk) AS kc
   FROM
     (SELECT pd,
             locks.lk AS 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,
            lk) p
GROUP BY pd
HAVING count(pd) = sum(c <= kc)

参考文献:fiddle