SQL语句

[popnew]

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语句

[popnew]

oo

[popnew]

ding

[popnew]

ding

[realgz]

是不是不是工人就是经理？
如果是：

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)
可以求出名字，剩下的就简单了
如果不是

[playyuer]

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]

想错了，应该是：
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)

[realgz]

或者
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]

如果需要显示是经理或者员工就变成
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

[popnew]

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]

复制粘贴的时候错了是
是e不是w2。。。