时间戳

首页 > 解决方案 > 如何选择在一个项目上一起工作时间最长的员工？

sql - 如何选择在一个项目上一起工作时间最长的员工？

问题描述

我有一个带有示例名称员工的表，其中保存了每个员工的 id、他们正在从事或已经从事的项目 ID、每个员工开始从事该项目的日期以及他们完成该项目的日期.

EmployeeID -   ProjectID   -    DateStart   -    DateEnd
-------------------------------------------------------------
1          -     100       -    22/11/2017   -   14/12/2017 
2          -     101       -    02/01/2017   -   19/10/2017
3          -     102       -    05/08/2017   -   09/07/2017
4          -     102       -    04/03/2017   -   12/03/2017
5          -     100       -    06/01/2017   -   22/12/2017
6          -     100       -    16/01/2017   -   22/12/2017
7          -     100       -    26/01/2017   -   22/07/2018
7          -     102       -    12/01/2017   -   22/12/2017
3          -     100       -    04/01/2017   -   20/11/2018
5          -     102       -    03/01/2017   -   22/10/2018

我需要编写一个选择查询来查找在一个项目中一起工作时间最长的几个员工。你能解释一下这应该如何发生吗？对我来说最困难的事情是如何检测两名员工是否同时在一个项目上工作——这就是我没有发布任何“尝试形式”的原因，因为我所有的尝试都是自我内部加入，例如

SELECT 
    A.EmployeeID, A.ProjectID, A.DateFrom, B.DateTo 
FROM 
    Employees A
INNER JOIN 
    Employees B on A.ProjectID = B.ProjectID

标签： sqlsql-servertsql

解决方案

projectid首先在 common s 和employeeid一个比另一个大的s上将 table 与自身连接起来。这让你得到了有趣的员工对以及这对夫妇一直在从事的项目。然后取他们两个 s 中较大的一个和他们两个datestarts 中较小的一个dateend。筛选开始小于结束的那些。用于datediff()获取这两个日期之间的天数。rank()与ORDER BY降序日期之间的持续时间一起使用。这样，持续时间最长的记录将获得1分配的排名。现在从该集合中选择排名等于 的所有记录1。

SELECT x.employeeid1,
       x.employeeid2,
       x.projectid,
       x.datestart,
       x.dateend,
       x.days
       FROM (SELECT t1.employeeid employeeid1,
                    t2.employeeid employeeid2,
                    t1.projectid,
                    CASE
                      WHEN t1.datestart > t2.datestart THEN
                        t1.datestart
                      ELSE
                        t2.datestart
                    END datestart,
                    CASE
                      WHEN t1.dateend < t2.dateend THEN
                        t1.dateend
                      ELSE
                        t2.dateend
                    END dateend,
                    datediff(day,
                             CASE
                               WHEN t1.datestart > t2.datestart THEN
                                 t1.datestart
                               ELSE
                                 t2.datestart
                             END,
                             CASE
                               WHEN t1.dateend < t2.dateend THEN
                                 t1.dateend
                               ELSE
                                 t2.dateend
                             END) days,
                    rank() OVER (ORDER BY datediff(day,
                                                   CASE
                                                     WHEN t1.datestart > t2.datestart THEN
                                                       t1.datestart
                                                     ELSE
                                                       t2.datestart
                                                   END,
                                                   CASE
                                                     WHEN t1.dateend < t2.dateend THEN
                                                       t1.dateend
                                                     ELSE
                                                       t2.dateend
                                                   END) DESC) r
                    FROM elbat t1
                         INNER JOIN elbat t2
                                    ON t2.projectid = t1.projectid
                                       AND t2.employeeid > t1.employeeid
                    WHERE CASE
                            WHEN t1.datestart > t2.datestart THEN
                              t1.datestart
                            ELSE
                              t2.datestart
                          END
                          <
                          CASE
                            WHEN t1.dateend < t2.dateend THEN
                              t1.dateend
                            ELSE
                              t2.dateend
                          END) x
       WHERE x.r = 1;

db<>小提琴

推荐阅读