我需要从雇员表中选择主管和非主管的姓名。因此,我需要返回两个单独的列,一个用于主管的姓名,一个用于雇员的姓名。
为此,我试着像这样使用where exists
select concat(first_name, middle_name, last_name) as supervisor_name, concat(first_name, middle_name, ulast_name) as employee_name
from employees
where exists (select employee_name from employees where employees.id = department.supervisor_id);
我还尝试在两个select子句之间创建一个联合,如下所示:
select concat (first_name, middle_name, last_name) as supervisor_name
from employees
where exists (select * from department where employees.id = department.supervisor_id)
union
select concat (first_name, middle_name, last_name) as employee_name
from femployees
where exists (select * from department where employees.id != department.supervisor_id);
请注意,部门是另一个表,其中包含主管的ID号。
我曾搜索过是否可以使用某种check
约束作为替代,但没有找到。我还尝试应用select distinct
,试图“除”返回的值,但无法使其工作。
我也尝试过使用别名,但它返回字段中的first_name
是不明确的。
select concat (first_name, middle_name, last_name) as supervisor_name, concat (first_name, middle_name, last_name) as employee_name
from employees
join employees as supervisor_name on department.supervisor_id = employees.id
join employees as supervisor_id on department.supervisor_id != employees.id;
1条答案
按热度按时间41ik7eoe1#
必须与
department
表联接。您需要在
SELECT
列表中使用表别名以避免歧义。