SQL查询需求：查询同一医院不同科室的医生姓名

现有Doctor表结构及数据如下：

需求：编写SQL查询语句，返回属于同一医院但科室不同的医生姓名。

方法1：自连接（新手友好） #

通过将Doctor表与自身关联，匹配同医院但不同科室的医生对，同时处理重复结果和无效数据：

SELECT DISTINCT d1.`Doctor'name`
FROM Doctor d1
JOIN Doctor d2 
  ON d1.`Hospital name` = d2.`Hospital name`
  AND d1.dept != d2.dept
WHERE d1.`Hospital name` IS NOT NULL;

逻辑说明：

• JOIN Doctor d2：把表自身当作另一张表，用来查找同医院的其他医生
• d1.Hospital name = d2.Hospital name``：确保两个医生属于同一家医院
• d1.dept != d2.dept：筛选出科室不同的医生对
• DISTINCT：避免同一个医生被重复返回（比如rajib和saurabh互相匹配，去重后只保留一次）
• WHERE d1.Hospital name IS NOT NULL：排除医院信息为空的Raj，因为他没有同医院的医生可匹配

方法2：窗口函数（更高效） #

通过窗口函数统计每个医院的不同科室数量，直接筛选出所在医院存在多科室的医生：

SELECT `Doctor'name`
FROM (
  SELECT 
    `Doctor'name`,
    `Hospital name`,
    COUNT(DISTINCT dept) OVER (PARTITION BY `Hospital name`) AS dept_count
  FROM Doctor
  WHERE `Hospital name` IS NOT NULL
) t
WHERE dept_count > 1;

逻辑说明：

• 子查询中用PARTITION BY Hospital name``按医院分组，COUNT(DISTINCT dept)统计每组内的不同科室数量
• 外层筛选dept_count > 1的记录：这类医生所在的医院至少有两个不同科室，自然满足"同医院但科室不同"的要求

两种方法都会返回以下结果：

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