MYSQL查询，用于获取具有多个类别的记录的编号

Question

所以我有一个表jobcatlist，其中有两列certification_id和JobCategory_id1个certification_id可以有多个作业类别类似(学前1、小学2、初中3、中学4(

我准备了第一个查询，以获得多个类别的证书总数该查询

SELECT certification_id, COUNT(certification_id) AS cert_count
FROM jobcatlist 
GROUP BY certification_id
HAVING COUNT(certification_id) > 1

在我看来是正确的，如果不是，请告诉我

所以我遇到了第二个问题，我想获得多个类别的认证，其中有特定类别，如学前1

我正在尝试此查询，但不正确

SELECT certification_id, COUNT(certification_id) AS cert_count
FROM jobcatlist 
WHERE jobcategory_id = 1
GROUP BY certification_id
HAVING  COUNT(certification_id) > 1 
ORDER BY certification_id DESC

Answer 1

WHERE子句：

WHERE jobcategory_id = 1

过滤掉所有其他jobcategory_id，因此，假设certification_id和jobcategory_id的组合是唯一的，COUNT()总是返回1。

删除WHERE子句，并在HAVING子句中添加条件：

SELECT certification_id, 
COUNT(*) AS cert_count
FROM jobcatlist 
GROUP BY certification_id
HAVING cert_count > 1 AND SUM(jobcategory_id = 1) > 0 
-- or HAVING cert_count > 1 AND MAX(jobcategory_id = 1) = 1
ORDER BY certification_id DESC;