如何使用SQL Server查询同专科但不同城市的医生？

需求与问题 #

需要筛选出所有同专科但来自不同城市的医生，尝试了两段SQL代码但未得到预期结果。

你尝试的代码 #

代码1 #

select * from doctors s
    join doctors c
    on s.specialty = c.specialty 
    and c.doctor_id <> s.doctor_id
where s.city <> c.city

代码2 #

SELECT DISTINCT d1.first_name, 
    d1.specialty,
    d1.city AS city1,
    d2.city AS city2
FROM doctors d1
JOIN doctors d2 ON d1.specialty = d2.specialty 
AND d1.doctor_id <> d2.doctor_id
WHERE d1.city <> d2.city;

问题原因 #

你的代码会产生重复记录（比如医生A和B匹配一次，B和A又会匹配一次），如果需求是获取符合条件的医生个体而非两两配对，原逻辑就不符合预期；即使要配对，也没做去重处理。

修正方案 #

方案1：获取所有符合条件的医生个体 #

如果要找出所有至少有一位同专科但不同城市同行的医生，用EXISTS子查询更高效且无重复：

SELECT DISTINCT d.*
FROM doctors d
WHERE EXISTS (
    SELECT 1
    FROM doctors d2
    WHERE d2.specialty = d.specialty
      AND d2.doctor_id != d.doctor_id
      AND d2.city != d.city
);

方案2：获取去重后的医生配对关系 #

如果需要展示同专科不同城市的医生配对，且避免重复组合（比如只保留A-B，不保留B-A），可以通过doctor_id的大小排序过滤：

SELECT DISTINCT
    d1.first_name 医生1姓名,
    d1.city 医生1城市,
    d1.specialty 专科,
    d2.first_name 医生2姓名,
    d2.city 医生2城市
FROM doctors d1
JOIN doctors d2 
    ON d1.specialty = d2.specialty 
    AND d1.doctor_id < d2.doctor_id  -- 确保每个配对只出现一次
    AND d1.city != d2.city;

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