MySQL查询：GROUP_CONCAT

问题描述：

我有两个表。一个叫做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)

但是我没有收到任何结果。我应该如何去做这样的事情？

在此先感谢。

不是用英文描述表格结构，而是以'CREATE'语句的形式使用[示例代码]（http://sscce.org/）。它更清晰。另外，样本数据（作为'INSERT'语句）和期望的结果是有帮助的。 – outis

答

给这一去。的cert_id名单上创建作业证书内嵌视图，另一个用于用户证书然后inner join在一起（确保存在这样GROUP_CONCAT的order by使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;

答

一个关系模型的原则之一是列值应是sim而不是复合;在白话中，它被称为first normal form（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)以获取缺失认证的数量。

MySQL查询：GROUP_CONCAT

相关推荐