MySQL 查询：GROUP_CONCAT

Question

我需要帮助构建查询。

我有两张桌子。一个称为 jobs_certs，另一个称为 users_certs。

jobs_certs 表包含申请职位所需的证书。每个所需的证书都有其自己的行，其中 job_id 代表所需证书所附加的职位发布，cert_id 代表附加到该证书的 ID。

users_certs 表包含用户拥有的所有证书。每个证书都有自己的行，其中 uid 代表用户的 id，cert_id 代表用户拥有的证书的 id。

我需要查看用户是否拥有该工作所需的所有证书。所以我尝试了这样的事情：

SELECT GROUP_CONCAT(cert_id) as certs, uid 
FROM users_certs 
HAVING certs = (SELECT GROUP_CONCAT(cert_id) FROM jobs_certs WHERE job_id = 6)

但我没有得到任何结果。类似这样的事情我该怎么办？

提前致谢。

Answer 1

试一试吧。为作业证书创建一个内联视图，为用户证书创建另一个内联视图，然后在 cert_id 列表上将它们内部联接在一起（确保有一个 order by 中的 GROUP_CONCAT 以使 GROUP_CONCAT 的结果具有确定性）：

select users.uid
from
(SELECT GROUP_CONCAT(cert_id order by cert_id asc) as certs, uid 
FROM users_certs 
group by uid) users
inner join
(SELECT GROUP_CONCAT(cert_id order by cert_id asc) as certs, job_id 
FROM jobs_certs
where job_id = 6
group by job_id) jobs
on jobs.certs = users.certs;

或者您甚至可以稍微修饰一下查询以返回所有用户，无论他们是否有所需的证书或不带有 hasRequiredCerts 列：

select users.uid,case when job_id is null then 'No' else 'Yes' end as hasRequiredCerts
from
(SELECT GROUP_CONCAT(cert_id order by cert_id asc) as certs, uid 
FROM users_certs 
group by uid) users
left join
(SELECT GROUP_CONCAT(cert_id order by cert_id asc) as certs, job_id 
FROM jobs_certs
where job_id = 6 
group by job_id) jobs
on jobs.certs = users.certs;

Answer 2

关系模型的原则之一是列值应该是简单的而不是复合的；用白话来说，它被称为第一范式 (1NF)。虽然 GROUP_CONCAT 有有效的用途（它会产生非 1NF 的关系），但它会让您陷入困境。相反，应关注所需的结果：选择用户在某项工作中缺少的认证。您可以通过在认证 ID 上进行左连接或右连接来完成此操作，选择用户为 NULL 的那些行。

SELECT jc.cert_id
    FROM jobs_certs AS jc
    LEFT JOIN users_certs AS uc ON jc.cert_id = uc.cert_id
    WHERE jc.job_id = ? AND uc.cert_id IS NULL

如果没有缺失任何认证，则用户完全合格。或者，您可以选择 COUNT(jc.cert_id) 来获取缺少的认证数量。