如何用一句sql判断两个员工在同一个部门？

[dong03]

ID 部门ID 员工名字

DM_ID Dept_ID Staff_Name
36 1 员工甲
37 1 员工乙
38 1 员工丙
39 3 员工甲
40 3 员工乙
41 5 员工丙
42 5 员工甲
48 1 员工丁
50 5 员工乙

如何用一句sql判断员工甲和员工乙是否在同一部门？

[zjcxc]

select case a.dept_id when b.deptid then '同一部门' else '不同部门' end
from(select dept_id from 表 where staff_name='员工甲') a left join(select dept_id from 表 where staff_name='员工乙') b on a.dept_id=b.dept_id

[kinglccs]

haha .用一个内连接不就可以了吗？为什么大家写的都这么难呢？

[txlicenhe]

Select '是在同一部门' where exists
(Select Dept_ID from 表 where staff_Name = '员工乙' and Dept_ID in (Select Dept_ID from 表 where staff_Name = '员工甲') )

[txlicenhe]

Select Dept_ID from 表 where staff_Name = '员工乙' and Dept_ID in (Select Dept_ID from 表 where staff_Name = '员工甲')

[pengdali]

if exists (select 1 from 表 a,表 b where a.Staff_Name='员工甲' and b.Staff_Name='员工乙' and a.Dept_ID=b.Dept_ID)
select '在'

[lvltt]

Select Dept_ID from 表 where staff_Name = '员工甲' and Dept_ID in (Select Dept_ID from 表 where staff_Name = '员工乙')

[pengdali]

select * from 表 where Staff_Name='员工甲' and dept_id in (select dept_id from 表 where Staff_Name='员工乙')

[realgz]

select case when exists
(select 1 from [table] t ,[table ] t2
where t.Staff_Name in ('员工甲','员工乙') and t2.Staff_Name in ('员工甲','员工乙') and t.dept_id=t2.dept_id and t.Staff_Name<>t2.Staff_Name ) then '在同一部门' else
'不在同一部门' end
今天怎么写得这么复杂？回去睡觉。。。。。。

[银狐被占用]

select * from 表 where (select Dept_ID from 表 where staff_Name = '员工甲') as Dept_ID1 in (select Dept_ID from 表 where staff_Name = '员工乙') as Dept_ID2