mysql 使用 AND 进行子查询 - 过滤结果集

Question

在mysql中，使用连接表employees_positions在employees和positions表之间建立m-n关系。我找到了一种传递position_id并检索具有该职位的员工的方法。

但是，我也会传递两个[或更多]position_id，并检索同时传递了两个[或全部]position_id 的员工。

例如，寻找开发人员和经理将返回 BOB 和 FRANK

数据库创建代码:

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ;

INSERT INTO `employees` (`id`, `name`) VALUES
    (1, 'bob'),
    (2, 'frank'),
    (3, 'jim'),
    (4, 'paul'),
    (5, 'mick');

CREATE TABLE `employees_positions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employe_id` int(11) NOT NULL,
  `position_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Index 2` (`position_id`,`employe_id`)
);

INSERT INTO `employees_positions` (`id`, `employe_id`, `position_id`) VALUES
    (1, 1, 1),
    (2, 2, 1),
    (3, 3, 3),
    (4, 4, 3),
    (5, 5, 3),
    (6, 1, 2),
    (7, 1, 3),
    (8, 2, 2);

CREATE TABLE `positions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ;

INSERT INTO `positions` (`id`, `name`) VALUES
    (1, 'manager'),
    (2, 'developer'),
    (3, 'executive');

目前我正在使用此查询来检索返回员工的职位:

SELECT
  name,
  (SELECT
      GROUP_CONCAT(p.name)
    FROM positions AS p
      LEFT JOIN employees_positions AS e_p
        ON p.id = e_p.position_id
    WHERE e_p.employe_id = e.id) AS positionsHeld,
  (SELECT
      GROUP_CONCAT(p.id)
    FROM positions AS p
      LEFT JOIN employees_positions AS e_p
        ON p.id = e_p.position_id
    WHERE e_p.employe_id = e.id) AS positionIDs

FROM employees AS e;

但我只想返回具有所有请求的positions_id的元素

Answer 1

Bohemian 给出了一个很好的答案，只要员工只被分配到一个职位一次。
另一种方法是:

select emp.id, emp.name, 
       group_concat(p.name) as positions_held, 
       group_concat(p.id) as position_ids
  from employees emp
  join employees_positions epos on epos.employe_id = emp.id
  join positions p on p.id = epos.position_id
  join employees_positions epos1 on epos1.employe_id = emp.id and epos1.position_id = 1
  join employees_positions epos2 on epos2.employe_id = emp.id and epos2.position_id = 2
group by emp.id, emp.name;

当您添加更多位置时，您只需添加更多行，例如:

  join employees_positions epos1 on epos1.employe_id = emp.id and epos1.position_id = 1

group_concat 将为您提供该员工所拥有的所有职位的列表，而不仅仅是与所提供职位匹配的职位。但提供的位置数据是静态的，不会逐行更改，因此您可以使用应用程序插入该数据。

Link to SQL Fiddle