提问人：Payamah 提问时间：8/9/2023 最后编辑：philipxyPayamah 更新时间：8/30/2023 访问量：56
如何使用 SQL Server 查找在同一专业工作但居住在不同城市的医生
How do I find doctors who work in the same specialty but live in different cities using SQL Server
问：
目标是选择所有在同一专业工作但来自不同城市的医生。我尝试了下面的两个代码选项，但它们没有产生预期的结果。
select * from doctors s
join doctors c
on s.specialty = c.specialty
and c.doctor_id <> s.doctor_id
where s.city <> c.city
SELECT DISTINCT d1.first_name,
d1.specialty,
d1.city AS city1,
d2.city AS city2
FROM doctors d1`your text`
JOIN doctors d2 ON d1.specialty = d2.specialty
AND d1.doctor_id <> d2.doctor_id
WHERE d1.city <> d2.city;`your text`
答：
1赞
myrn
8/9/2023
#1
试试这个查询;
SELECT DISTINCT d1.first_name,
d1.specialty,
d1.city AS city1,
d2.city AS city2
FROM doctors d1
JOIN doctors d2 ON d1.doctor_id <> d2.doctor_id
WHERE d1.city <> d2.city and d1.specialty = d2.specialty
此外，您的联接条件应该只是 id 的不等式。而你的条件应该是城市和专业。
0赞
nvogel
8/29/2023
#2
你的问题有点不清楚。你的意思是你希望所有医生都符合这种情况，例如：
SELECT *
FROM doctors AS d
WHERE city != ANY
(SELECT city
FROM doctors AS e
WHERE d.specialty = e.specialty);
或者你真的希望所有对医生都满足这样的条件：
SELECT d.doctor_id, d.first_name, d.last_name, d.city,
e.doctor_id, e.first_name, e.last_name, e.city
FROM doctors AS d, doctors AS e
WHERE d.specialty = e.specialty
AND d.city != e.city;
我假设doctor_id在您的表格中是独一无二的，因此每个医生只属于一个城市。如果这是不正确的，那么请告诉我们您的表的键是什么。更好的是，发布 CREATE TABLE 语句。希望其中一个查询接近您的需求。
评论