none
sql复杂查询请教 RRS feed

  • 问题

  • HR数据库有三个表:

    1、员工表:employee(eid,ename,dept,rang)

    其中eid为员工编号,ename为员工姓名,dept为所在部门,rank为级别。该表记录当前人员情况。

    示例数据:

    eid

    ename

    dept

    rank

    1

    name1

    dept2

    engineer

    2

    name2

    dept1

    NULL

    2、部门变动历史表:dept_history(dhid, eid, ename,old, new, uptime)

    其中dhid为部门变动编号(自动增长,无需赋值),eid为员工编号,ename为员工姓名,old为调动前的部门(新入职的员工old值记为NULL),new为调动后的部门(离职的员工new 值记为NULL),uptime为修改时间。

    示例数据:

    dhid

    eid

    name

    old

    new

    update

    1

    1

    name1

    NULL

    dept1

    2020-01-01

    2

    1

    name1

    dept1

    dept2

    2021-06-01

    3

    3

    name3

    NULL

    dept2

    2020-01-01

    4

    3

    name3

    dept2

    NULL

    2021-04-30

    3、级别变动历史表:rank_history(rhid, eid,ename, old, new, uptime)

    其中drid为级别变动编号(自动增长,无需赋值),eid为员工编号,ename为员工姓名,old为变动前的级别,new为变动后的级别,无记录做NULL值处理,uptime为修改时间。

    示例数据:

    rhid

    eid

    name

    old

    new

    update

    1

    1

    name1

    NULL

    technician

    2020-10-01

    2

    1

    name1

    technician

    engineer

    2021-02-01

    4、要求:用sql语句,根据给定的任意历史时间,得到该时间点的员工表。如要得到2021-03-30的员工表应为:

    eid

    ename

    dept

    rank

    1

    name1

    dept1

    technician

    2

    name2

    dept1

    NULL

    3

    name3

    dept2

    NULL

    2021年9月1日 6:28

答案

  • dept_history表数据比较完整也有ename字段的话可以不用employee表

    declare @historydate date;
    set @historydate = '2021-06-02';
    select m.eid,m.ename,m.dept,n.[rank] from (
    select a.eid,a.ename,a.new dept from dept_history a join(
    select eid,max([update]) [update] from dept_history where [update]<=@historydate group by eid) b on a.eid=b.eid and a.[update]=b.[update]
     ) m left join (
    select a.eid,a.new rank from rank_history a join(
    select eid,max([UPDATE]) [update] from rank_history where [update]<=@historydate group by eid) b on a.eid=b.eid and a.[update]=b.[update]
    ) n on m.eid=n.eid where m.dept is not null

    • 已标记为答案 123kaca 2021年9月3日 6:46
    2021年9月2日 16:41

全部回复

  • declare @historydate date;
    set @historydate = '2020-03-30';
    select h.eid,h.ename,isnull(n.new,h.dept) dept,m.new [rank] from employee h left join (
    select a.* from dept_history a join(
    select eid,max([update]) [update] from dept_history where [update]<=@historydate group by eid) b on a.eid=b.eid and a.[update]=b.[update]
    ) n on h.eid=n.eid left join(
    select a.* from rank_history a join(
    select eid,max([UPDATE]) [update] from rank_history where [update]<=@historydate group by eid) b on a.eid=b.eid and a.[update]=b.[update]
    ) m on h.eid=m.eid where isnull(n.new,h.dept) is not null
    dept_historydate表中有eid=2的数据的话会更简单点
    • 已编辑 LiXYong 2021年9月2日 16:29 没有筛选离职的数据
    2021年9月1日 14:07
  • Hi @123kaca,

    请尝试以下T-SQL语句;
    create table employee(
    eid int,
    ename varchar(10),
    dept varchar(10),
    rank varchar(10))
    
    
    insert into employee values
    (1,'name1','dept2','engineer'),
    (2,'name2','dept1',NULL)
    
    
    create table dept_history(
    dhid int identity(1,1),
    eid int,
    ename varchar(10) ,
    old varchar(10),
    new varchar(10),
    uptime date)
    
    
    insert into dept_history (eid, ename,old, new, uptime) values
    (1,'name1',NULL,'dept1','2020-01-01'),
    (1,'name1','dept1','dept2','2021-06-01'),
    (3,'name3',NULL,'dept2','2020-01-01'),
    (3,'name3','dept2',NULL,'2021-04-30')
    
    
    
    create table rank_history(
    rhid int identity(1,1),
    eid int,
    ename varchar(10),
    old varchar(10),
    new varchar(10),
    uptime date)
    
    
    insert into rank_history (eid,ename, old, new, uptime) values
    (1,'name1',NUll,'technician','2020-10-01'),
    (1,'name1','technician','engineer','2021-02-01');
    
    
    
    declare @history date='2021-03-30'
    
    ;with cte as (
    select a.eid,a.ename,a.new dept
    from dept_history a
    inner join
    (select eid,max(uptime) maxtime
    from dept_history
    where uptime<=@history
    group by eid) b
    on a.eid=b.eid and a.uptime=b.maxtime)
    ,cte1 as (
    select a.eid,a.ename,a.new rank from rank_history a
    inner join
    (select eid,max(uptime) maxtime
    from rank_history
    where uptime<=@history
    group by eid) b
    on a.eid=b.eid and a.uptime=b.maxtime)
    select a.eid,a.ename,a.dept,b.rank
    from cte a
    left join cte1 b on a.eid=b.eid
    union
    select eid,ename,dept,rank from employee
    where eid not in (select eid from cte)
    order by eid

    ******************************************************************************************
    如果回复有帮助,请点击“接受答案”并点赞,因为这可以帮助其他社区成员寻找类似的帖子。



    2021年9月2日 7:14
  • 谢谢你的指导。从dept_history表可以得出:name3在2021-04-30离职,即在2021-05-01后,他不应存在于employee表中。但是,无论我怎样改变@history的值,他仍然在。
    2021年9月2日 13:51
  • 谢谢!确实是少了eid=2的测试数据。假定其记录为:5,2,name2,NULL,dept1,2020-02-01
    2021年9月2日 13:53
  • 为了方便测试,以下数据供参考:

    当前的人员情况employee

    eid

    ename

    dept

    rank

    1

    name1

    dept2

    engineer

    2

    name2

    dept1

    NULL

    人员调动记录dept_history

    dhid

    eid

    ename

    old

    new

    uptime

    1

    1

    name1

    NULL

    dept1

    2020/1/1

    2

    1

    name1

    dept1

    dept2

    2021/6/1

    3

    3

    name3

    NULL

    dept2

    2020/1/1

    4

    3

    name3

    dept2

    NULL

    2021/4/30

    5

    2

    name2

    NULL

    dept1

    2020/2/1

    人员级别记录rank_history

    rhid

    eid

    ename

    old

    new

    uptime

    1

    1

    name1

    NULL

    technician

    2020/10/1

    2

    1

    name1

    technician

    engineer

    2021/2/1

    2020/1/2人员情况(2020/1/1之前应为空表)

    eid

    ename

    dept

    rank

    1

    name1

    dept1

    NULL

    3

    name3

    dept2

    NULL

    2020/2/2 人员情况

    eid

    ename

    dept

    rank

    1

    name1

    dept1

    NULL

    2

    name2

    dept1

    NULL

    3

    name3

    dept2

    NULL

    2020/10/2人员情况

    eid

    ename

    dept

    rank

    1

    name1

    dept1

    engineer

    2

    name2

    dept1

    NULL

    3

    name3

    dept2

    NULL

    2021/2/2人员情况

    eid

    ename

    dept

    rank

    1

    name1

    dept1

    engineer

    2

    name2

    dept1

    NULL

    3

    name3

    dept2

    NULL

    2021/5/1人员情况

    eid

    ename

    dept

    rank

    1

    name1

    dept1

    engineer

    2

    name2

    dept1

    NULL

    2021/6/2人员情况(与当前情况相同)

    eid

    ename

    dept

    rank

    1

    name1

    Dept2

    engineer

    2

    name2

    dept1

    NULL

    2021年9月2日 15:05
  • dept_history表数据比较完整也有ename字段的话可以不用employee表

    declare @historydate date;
    set @historydate = '2021-06-02';
    select m.eid,m.ename,m.dept,n.[rank] from (
    select a.eid,a.ename,a.new dept from dept_history a join(
    select eid,max([update]) [update] from dept_history where [update]<=@historydate group by eid) b on a.eid=b.eid and a.[update]=b.[update]
     ) m left join (
    select a.eid,a.new rank from rank_history a join(
    select eid,max([UPDATE]) [update] from rank_history where [update]<=@historydate group by eid) b on a.eid=b.eid and a.[update]=b.[update]
    ) n on m.eid=n.eid where m.dept is not null

    • 已标记为答案 123kaca 2021年9月3日 6:46
    2021年9月2日 16:41
  • 谢谢你的指导。从dept_history表可以得出:name3在2021-04-30离职,即在2021-05-01后,他不应存在于employee表中。但是,无论我怎样改变@history的值,他仍然在。

          与其将sql查询语句搞复杂,不如修改一下数据表结构,将问题搞简单。

          员工表:employee(eid,ename,dept,rank)增加一个字段resign,0表示在职,1表示离职,所有在职和离职员工记录全部保存在employee表中。

    2021年9月3日 5:55
  • 现实中,一个employee的评估维度不止dept和rank二个,一般会有十几个吧,这里是为了简便,只举例2个。而且,某个employee是否在职,是与时间点有关的。在某个时间点,name3在职,在另外一个时间点,name3有可能不在职。当然,也可以在employee中增加uptime字段,每个月(假定最小时间粒度是自然月,当然也有可能是自然日)将所有employee的信息固定下来,是可以的。


    • 已编辑 123kaca 2021年9月3日 6:18
    2021年9月3日 6:11
  • 员工表:employee表的在职和离职只是针对当前时间点来说,没有必要把每个时间点的在离职情况都反映出来,

    部门变动历史表:dept_history可以反映出在离职的时间点,只有在表结构设计完善的基础上,才能做出简洁清晰的SQL查询语句。

    2021年9月3日 6:52