MySQL:选择联接表匹配所有值的记录 [英] MySQL: Select records where joined table matches ALL values
本文介绍了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);
这是我到目前为止的查询:
Here is the query I have so far:
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:选择联接表匹配所有值的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文