MySQL:选择连接表匹配所有值的记录

问题描述

我正在努力寻找所有拥有多种技能的员工.以下是表格:

I'm trying to find all employees with multiple skills. Here are the tables:

CREATE TABLE IF NOT EXISTS `Employee` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
INSERT INTO `Employee` (`ID`, `Name`, `Region_ID`) VALUES (1, 'Fred Flintstone'), (2, 'Barney Rubble');

CREATE TABLE IF NOT EXISTS `Skill` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
INSERT INTO `Skill` (`ID`, `Name`) VALUES (1, 'PHP'), (2, 'JQuery');

CREATE TABLE IF NOT EXISTS `Emp_Skills` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Emp_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  `Skill_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
INSERT INTO `Emp_Skills` (`ID`, `Emp_ID`, `Skill_ID`) VALUES (1, 1, 1), (2, 1, 2), (3, 2, 1);

这是我目前的查询:

SELECT DISTINCT(em.ID), em.Name 
FROM Employee em 
INNER JOIN Emp_Skills es ON es.Emp_ID = em.ID
WHERE es.Skill_ID IN ('1', '2')

这会返回两个员工，但是，我需要找到同时拥有这两种技能(ID 1 和 2)的员工.

This returns both employees, however, I need to find the employee that has both skills (ID 1 and 2).

有什么想法吗?谢谢

推荐答案

这样做:

SELECT EmpId, Name
FROM
(
   SELECT em.ID as EmpId, em.Name, es.ID as SkillID 
   FROM Employee em 
   INNER JOIN Emp_Skills es ON es.Emp_ID = em.ID
   WHERE es.Skill_ID IN ('1', '2')
 ) X
GROUP BY EmpID, Name
HAVING COUNT(DISTINCT SkillID) = 2;

在这里小提琴:

区别只是以防同一个员工将技能列出两次.

The distinct is just in case the same employee has the skill listed twice.

感谢提供测试数据.

这篇关于MySQL:选择连接表匹配所有值的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！