mysql - 如何在这个sql表中找到所有员工访问过的位置？

Question

我有一个如下所示的 sql 表:

CREATE TABLE `location_visits` (
  `employee1_name` varchar(255) NOT NULL,
  `employee2_name` varchar(255) NOT NULL,
  `region_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`employee1_name`,`employee2_name`),
  CONSTRAINT `location_visits_fk1` FOREIGN KEY (`employee1_name`) REFERENCES `employees` (`employee_name`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `location_visits_fk2` FOREIGN KEY (`employee2_name`) REFERENCES `employees` (`employee_name`) ON DELETE CASCADE ON UPDATE CASCADE,
)

员工结对一起去某个地方，有点像伙伴系统。我想找到所有员工访问过的区域。

如果有帮助，我还有一张列出我所有员工的表格:

CREATE TABLE `employees` (
  `employee_name` varchar(255) NOT NULL,
  PRIMARY KEY ('employee_name')
)

当然，所有这些表都加载了数据。因此，对于 location_visits 表有效的数据示例是 (John, Chris, Maine), (John, Adam, New Hampshire), (Adam, Chris, Maine)。在此场景中，所有员工 Adam、Chris、John 访问的位置是 Maine。

哪种 sql 命令会为我执行此操作？

Answer 1

您需要获取每个地区所有不同员工的数量，并获取员工总数等于员工表中的员工总数的地区。您的查询应如下所示:

SELECT COUNT(employee1_name), region_name FROM
  (SELECT DISTINCT employee1_name, region_name FROM
  (SELECT DISTINCT employee1_name, region_name FROM location_visits
UNION
SELECT DISTINCT employee2_name, region_name FROM location_visits))
GROUP BY region_name
HAVING COUNT(employee1_name) =
       (SELECT COUNT(DISTINCT employee_name) FROM employees);

希望这对您有所帮助!