我有一个员工数据库,员工多次出现。
同一员工的某些实例具有员工部门,而在其他一些情况下,缺少员工部门。为此,我需要找到与员工部门不同的员工姓名。
有些员工根本没有任何员工部门。为此,我只需要找到不同的员工姓名。
我的查询:
------for each unique employee, find department
--select columns
select
a.employee_name,b.employee_dept
from
--unique employee names
(
select
distinct employee_name
from
employeeDB
where
employee_name is not null and employee_name != ''
order by employee_name asc
) as a,
--employee name and employee dept sorted
(
select
employee_name,employee_dept
from
employeeDB
where employee_name is not null and employee_name != ''
order by employee_name asc, employee_dept desc
) as b
--condition for join
where
a.employee_name = b.employee_name
order by a.employee_name asc, b.employee_dept desc
limit 1000
我不确定为什么结果没有返回唯一的结果。我得到类似于下面的结果
Name Dept
A DepA
A DepB
A
B DepA
B
C DepZ
D
E
这将获得所有唯一的名称(通过将其分组)并employee_dept
获取每个字母可以找到的(字母顺序)最高的名称
select employee_name, max(employee_dept) as dept
from employeeDB
where employee_name is not null and employee_name != ''
group by employee_name
order by employee_name asc
limit 1000
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句