Question

I have a table of Padlocks which (unfortunately) consists of a column with comma-separated data. Table consists of padlocks with information of types of Locks given padlock has (and whether it s big or small). Given a key, which can unlock certain types of locks (and size) I want to find which Padlocks I would be able to unlock. A key has also a comma-separated Lock types and a size.

<>12>

Name	Locks	Size
A	KL, OK, CZ, CZ	Small
B	OK, OK	Small
C	OK, CZ, KL	Small
D	RO, CZ, CZ	Small
E	OK, OK, KL, KL	Small
F	OK, OK, CZ, KL	Big

(Input) Key (KL, OK, OK, OK, CZ, CZ , Small ) will 回去

Name
A
B
C

请注意,如果钥匙能够只打开一例KL,那么它不会锁定Padlock E,因为它需要双倍的KL, KL 。 Padlock D 不能因为需要RO而锁定。 Padlock 不能因为需要大钥匙而孤立。

如果是一些方案语言,如C#,I d分裂了对阵列[KL 、OK 、OK 、 CZ 、 CZ]的关键投入,而且对于每个行,我也将把洛克数分成阵列,对两个阵列进行比较。

Pseudocode

foreach Lock in Locks
for(i=0;i<UBound(Lock);i++)
 for(j=0;i<UBound(Key);j++)
  If(Lock[i]=Key[j]) Lock[i].Remove Key[j].Remove

If at some point Lock is left with 0 values then the Key can open that lock. But that is way over what I can do in MariaDB. I know of FIND_IN_SET() but that would let me search for one input only, unless there is a way to use it to fit my needs

PS Select @@version = 10.3.39-MariaDB-0+deb10u2

PS2 Would normalizing help? I mean, I could create a table Locks which would hold all the lock types and then create a cross reference table for Padlocks<->Locks. Would there be an SQL statement that would let me get what I need?

Answer 1

通过将锁定在新桌前,非常直截了当:

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

Then create a temporary table for the keys you have:

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

pd	lk	count(locks.lk)
A	CZ	2
A	KL	1
A	OK	1
B	OK	2
C	CZ	1
C	KL	1
C	OK	1
D	CZ	2
D	RO	1
E	KL	2
E	OK	2

Compare this to how many keys we have of each type:

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	lk	c	kc
A	CZ	2	2
A	KL	1	1
A	OK	1	2
B	OK	2	2
C	CZ	1	2
C	KL	1	1
C	OK	1	2
D	CZ	2	2
D	RO	1	0
E	KL	2	1
E	OK	2	2

现在按<代码>pd分类,再选用所有锁的钥匙多于锁。 <代码>(pd)为锁编号。 c <= kc, 如果钥匙不足,即为1比1,如果钥匙足够的话。对于所有锁定类型,应当有足够的钥匙。

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)

pd
A
B
C

参看:fiddle 。

友情链接