代码之家 › 专栏 › 技术社区 › Preethi N

根据start_time对夜班员工的任务进行排序

sorting database mysql sql php

Preethi N · 技术社区 · 1 年前

//night shift employee
id  from_time    to_time   task
1    21:00:00    22:00:00 - Cleaning(some task)
1    22:00:00    23:30:00 - Fumigation(can be some other task also)
1    4:00:00     7:00:00 - Disinfection
1    2:00:00     4:00:00 - Break
1    23:30:00    2:00:00 - Fogging

//day shift employee

2  09:00:00    10:00:00 - Cleaning(some task)
2  16:00:00    18:30:00 - Disinfection
2  11:30:00    14:00:00 - Fumigation(can be some other task also)
2  14:00:00    16:00:00 - Fogging
2  10:00:00    11:30:00 - Break

我需要根据from_time按升序对任务进行排序

SELECT * FROM testing WHERE emp_id='2' ORDER BY from_time ASC

以上查询适用于白班员工,但不适用于夜班员工

SELECT * FROM testing WHERE emp_id='1'
ORDER BY 
  CASE 
    WHEN CAST(from_time AS TIME) > '12:00:00' THEN 1
    ELSE 2
  END,
  CAST(from_time AS TIME) ASC;

以上查询适用于夜班员工,但不适用于白班员工

我该如何解决这个问题?

我需要一个查询,它可以根据from_time对夜班和白班员工的任务进行排序。

0 回复 | 直到 1 年前

user1191247 1 年前

你可以检查最小值和最大值之间的时间差 from_time 每班员工:

SELECT *
FROM testing t1
JOIN (
    SELECT id, TIMEDIFF(MAX(from_time), MIN(from_time)) AS diff
    FROM testing
    GROUP BY id
) t2 ON t1.id = t2.id
ORDER BY t1.id, IF(t2.diff > '12:00' AND from_time < '12:00', from_time + INTERVAL 24 HOUR, from_time);

输出:

身份证件	从时间	到时间(_T)	任务
1.	21:00:00	22:00:00	清洁(一些任务)
1.	22:00:00	23:30:00	熏蒸
1.	23:30:00	02:00:00	雾化
1.	02:00:00	04:00:00	打破
1.	04:00:00	07:00:00	消毒
2.	09:00:00	10:00:00	清洁(一些任务)
2.	10:00:00	11:30:00	打破
2.	11:30:00	14:00:00	熏蒸(也可以是其他任务)
2.	14:00:00	16:00:00	雾化
2.	16:00:00	18:30:00	消毒

这是一个 db<>fiddle .

最好以某种方式与轮班开始联系起来,但这可能取决于你的轮班模式。

d r 1 年前

将日期添加到FROM_TIME中,并在午夜后将其更正为第二天。您的订购人可能有以下代码:

         ADDTIME(DATE_ADD(CURRENT_DATE, 
                          INTERVAL 
                          Case When CONVERT(SubStr(FROM_TIME, 1, INSTR(FROM_TIME, ':') - 1), DECIMAL) < 12 And 
                                    SHIFT_ID = 1
                               Then 1 
                          Else 0 
                          End DAY), 
                 Cast(FROM_TIME as TIME))

这会将from_time添加到当前日期,如果from time hour小于12,则会为ID=1添加1天。如果你的夜班是从今天19:00到明天07:00,那么输入<7而不是<在case表达式的条件下为12。按此排序就是按DATETIME排序。
示例数据、SQL代码和结果如下:

WITH    --  S a m p l e    D a t a :
    shift_tasks (SHIFT_ID, FROM_TIME, TO_TIME, TASK) AS
        ( Select   1, '21:00:00', '22:00:00', 'Cleaning'     Union All
          Select   1, '22:00:00', '23:30:00', 'Fumigation'   Union All
          Select   1, '4:00:00',  '7:00:00',  'Disinfection' Union All
          Select   1, '2:00:00',  '4:00:00',  'Break'        Union All
          Select   1, '23:30:00', '2:00:00',  'Fogging'      Union All
          --
          Select   2, '09:00:00', '10:00:00', 'Cleaning'     Union All
          Select   2, '16:00:00', '18:30:00', 'Disinfection' Union All
          Select   2, '11:30:00', '14:00:00', 'Fumigation'   Union All
          Select   2, '14:00:00', '16:00:00', 'Fogging'      Union All
          Select   2, '10:00:00', '11:30:00', 'Break' 
        )

--    S Q L :
Select   SHIFT_ID, FROM_TIME, TO_TIME, TASK
From     shift_tasks
Order By SHIFT_ID, 
         ADDTIME(DATE_ADD(CURRENT_DATE, 
                          INTERVAL 
                          Case When CONVERT(SubStr(FROM_TIME, 1, INSTR(FROM_TIME, ':') - 1), DECIMAL) < 12 And 
                                    SHIFT_ID = 1
                               Then 1 
                          Else 0 
                          End DAY), 
                 Cast(FROM_TIME as TIME))

/*      R e s u l t :
SHIFT_ID    FROM_TIME   TO_TIME    TASK
--------    ---------   ---------  ----------------------
       1    21:00:00    22:00:00   Cleaning
       1    22:00:00    23:30:00   Fumigation
       1    23:30:00    2:00:00    Fogging
       1    2:00:00     4:00:00    Break
       1    4:00:00     7:00:00    Disinfection
       2    09:00:00    10:00:00   Cleaning
       2    10:00:00    11:30:00   Break
       2    11:30:00    14:00:00   Fumigation
       2    14:00:00    16:00:00   Fogging
       2    16:00:00    18:30:00   Disinfection          */

如果Order By子句中没有SHIFT_ID,结果应该是。。。

/*      R e s u l t :
SHIFT_ID    FROM_TIME   TO_TIME    TASK
--------    ---------   ---------  ----------------------
       2    09:00:00    10:00:00   Cleaning
       2    10:00:00    11:30:00   Break
       2    11:30:00    14:00:00   Fumigation
       2    14:00:00    16:00:00   Fogging
       2    16:00:00    18:30:00   Disinfection          
       1    21:00:00    22:00:00   Cleaning
       1    22:00:00    23:30:00   Fumigation
       1    23:30:00    2:00:00    Fogging
       1    2:00:00     4:00:00    Break
       1    4:00:00     7:00:00    Disinfection            */