SQL如何做交集查询

Question

有一张表

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id` bigint(64) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `real_name` varchar(64) DEFAULT NULL COMMENT '真名',
 `depart_id` varchar(512) DEFAULT '[]' COMMENT '部门id列表',
  `add_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='员工表';

INSERT INTO employee (id, real_name, depart_id,  add_time, modify_time) VALUES (1, '刘德华', '[2,3]',  '2023-05-25 06:23:18', '2023-09-21 10:39:44');
INSERT INTO employee (id, real_name, depart_id,  add_time, modify_time) VALUES (1, '梁朝伟', '[4,8,7]',  '2023-05-25 06:23:18', '2023-09-21 10:39:44');

员工可以从属于多部门，
需求是只要员工所属的部门ID和目标部门列表有交集，就出现在结果里，比如目标部门列表为(3, 8, 18, 19, 10, 11),刘德华所属的部门为2,3 所以属于符合搜索结果（因为有3），梁朝伟也是（因为有8）。
并且，这里有个要求，我们只能用一条SQL去实现，也不能用存储过程，如果用 select * from employee where depart_id in (3, 8, 18, 19, 10, 11) ,搜索是得不到任何值的，那么怎么写才对呢？

Answer 1

引用 皆我百晓生 小程序回应内容作答：

• 可以使用SQL中的JSON函数来实现交集查询。

以下是一种可能的解决方案：

SELECT *
FROM employee
WHERE JSON_OVERLAPS(depart_id, JSON_ARRAY(3, 8, 18, 19, 10, 11)) = 1;

这里使用了JSON_OVERLAPS函数来判断depart_id和目标部门列表之间是否有交集，JSON_OVERLAPS函数返回1表示有交集，返回0表示无交集。

注意：JSON_OVERLAPS函数在MySQL 8.0版本及以上可用。如果使用的是低于该版本的MySQL，可以考虑使用相关的JSON函数来进行交集查询。

• 另外您可以使用SQL的字符串函数和逻辑操作符来实现交集查询。以下是一个示例SQL查询，它使用FIND_IN_SET函数和逻辑操作符来计算员工部门列表和目标部门列表的交集，并返回符合条件的结果：

SELECT *
FROM employee
WHERE depart_id <> '[]' -- 排除空列表
AND (
    FIND_IN_SET('3', depart_id) > 0 -- 目标部门列表中是否包含3
    OR FIND_IN_SET('8', depart_id) > 0 -- 目标部门列表中是否包含8
    -- 继续添加其他目标部门的判断条件
);

请注意，这里假设depart_id字段存储的是逗号分隔的部门ID列表。如果您需要考虑列表中包含其他字符或分隔符，需要自行进行调整。同时，您还可以根据需要添加更多目标部门的判断条件，以满足具体的查询要求。