如何用MySQL查询获取一对一关联记录？现有查询未生效

原查询的问题 #

你的原查询仅过滤了同一名员工重复分配到同一门店的记录，但并没有实现真正的“一对一”关联——即一个员工仅对应唯一门店，且一个门店仅对应唯一员工。分组条件store_code, emp_code加上COUNT(store_code) = 1，只能确保每个(门店,员工)组合在表中不重复，但无法限制员工跨门店分配，也无法限制门店分配多名员工。

解决方案 #

要获取真正的一对一关联记录，需要从两个维度筛选：

1. 仅被分配到一个门店的员工
2. 仅分配了一个员工的门店

然后取同时满足这两个条件的记录，以下是可行的SQL语句：

完整查询（兼容重复记录场景）

如果表中存在同一名员工多次分配到同一门店的重复记录，使用COUNT(DISTINCT)确保统计的是不同的关联关系：

SELECT t.emp_code
FROM Test_app_emp_assign_to_store_ t
-- 筛选仅对应一个门店的员工
JOIN (
    SELECT emp_code
    FROM Test_app_emp_assign_to_store_
    WHERE store_code IN('AUGT0052', 'AUMT0289', 'AUGT0011', 'AUMT0270', 'AUGT0039', 'AUGT0176', 'AUGT3008', 'AUMT0333', 'AUGT0382')
    GROUP BY emp_code
    HAVING COUNT(DISTINCT store_code) = 1
) e ON t.emp_code = e.emp_code
-- 筛选仅对应一个员工的门店
JOIN (
    SELECT store_code
    FROM Test_app_emp_assign_to_store_
    WHERE store_code IN('AUGT0052', 'AUMT0289', 'AUGT0011', 'AUMT0270', 'AUGT0039', 'AUGT0176', 'AUGT3008', 'AUMT0333', 'AUGT0382')
    GROUP BY store_code
    HAVING COUNT(DISTINCT emp_code) = 1
) s ON t.store_code = s.store_code
WHERE t.store_code IN('AUGT0052', 'AUMT0289', 'AUGT0011', 'AUMT0270', 'AUGT0039', 'AUGT0176', 'AUGT3008', 'AUMT0333', 'AUGT0382');

简化查询（无重复记录场景）

如果你的表中(store_code, emp_code)是唯一组合（不存在重复分配的情况），可以去掉DISTINCT，用COUNT(*)提升查询性能：

SELECT t.emp_code
FROM Test_app_emp_assign_to_store_ t
JOIN (
    SELECT emp_code
    FROM Test_app_emp_assign_to_store_
    WHERE store_code IN('AUGT0052', 'AUMT0289', 'AUGT0011', 'AUMT0270', 'AUGT0039', 'AUGT0176', 'AUGT3008', 'AUMT0333', 'AUGT0382')
    GROUP BY emp_code
    HAVING COUNT(*) = 1
) e ON t.emp_code = e.emp_code
JOIN (
    SELECT store_code
    FROM Test_app_emp_assign_to_store_
    WHERE store_code IN('AUGT0052', 'AUMT0289', 'AUGT0011', 'AUMT0270', 'AUGT0039', 'AUGT0176', 'AUGT3008', 'AUMT0333', 'AUGT0382')
    GROUP BY store_code
    HAVING COUNT(*) = 1
) s ON t.store_code = s.store_code
WHERE t.store_code IN('AUGT0052', 'AUMT0289', 'AUGT0011', 'AUMT0270', 'AUGT0039', 'AUGT0176', 'AUGT3008', 'AUMT0333', 'AUGT0382');

逻辑说明 #

• 第一个子查询e：筛选出指定门店范围内，仅关联一个门店的员工，确保员工不会跨多个门店分配。
• 第二个子查询s：筛选出指定门店范围内，仅关联一个员工的门店，确保门店不会分配给多名员工。
• 最终通过关联原表和两个子查询，得到同时满足“员工唯一对应门店”和“门店唯一对应员工”的一对一关联记录。

内容的提问来源于stack exchange，提问作者Dinesh Sharma