SQL语句

popnew 2003-11-18 05:54:16
employee(employee_name ,street,city) employee_name键
works(employee_name ,company_name,salary) employee_name键
company(company_name, city) company_name键
manages(employee_name ,manager_name) employee_name键
求跟其经理在同一城市、同一街道的员工的信息
写出你的最简便的SQL语句

...全文
29 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
realgz 2003-11-19
  • 打赏
  • 举报
回复
复制粘贴的时候错了是
是e不是w2。。。
popnew 2003-11-18
  • 打赏
  • 举报
回复
select employee_name from employee where
exists (select 1 from employee e, manages m where e.street+e.city=employee.street+employee.city
and m.employee_name=w2.employee_name)

w2是什么
realgz 2003-11-18
  • 打赏
  • 举报
回复
如果需要显示是经理或者员工就变成
select employee_name,works.street,works.city,
(case when manages.manager_name is not null then manages.manager_name
else 'IS WORKER 'end ) as State
from employee,manages ,works
where
exists (select 1 from employee e, manages m where e.street+e.city=employee.street+employee.city
and m.employee_name=w2.employee_name)
and employee.employee_name *= manages.employee_name
and employee.employee_name *= works.employee_name
order by city,State
realgz 2003-11-18
  • 打赏
  • 举报
回复
或者
select employee_name from employee where
exists (select 1 from employee e, manages m where e.street+e.city=employee.street+employee.city
and m.employee_name=w2.employee_name)

是完美解,可以选出有再同一城市同一街道的经理和员工的名单。
realgz 2003-11-18
  • 打赏
  • 举报
回复
想错了,应该是:
select employee_name from employee where (select count(*) from employee e where e.street+e.city=employee.street+employee.city)
>(select count(*) from employee e, works w2 where e.street+e.city=employee.street+employee.city and e=w2.employee_name)
playyuer 2003-11-18
  • 打赏
  • 举报
回复
select *
,(select manager_name
from manages
where employee_name = a.employee_name
)
from employee a
where employee_name in (select employee_name
from manages
where manager_name in (select employee_name
from employee
where city = a.city
and street = a.street
)
)
realgz 2003-11-18
  • 打赏
  • 举报
回复
是不是不是工人就是经理?
如果是:

select employee_name from employee where (select count(*) from employee e where e.street+e.city=employee.street+employee.city)
>(select count(*) from works w2 where w2.employee_name=employee.employee_name)
可以求出名字,剩下的就简单了
如果不是
popnew 2003-11-18
  • 打赏
  • 举报
回复
ding
popnew 2003-11-18
  • 打赏
  • 举报
回复
ding
popnew 2003-11-18
  • 打赏
  • 举报
回复
oo

34,566

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧