mysql - 带条件乘法的sql语句应该怎么写？

Question

假设我有如下表格:

日程安排

+-------+------------+--------+----------------+-------+---------+
| index | date       | empid  | dept           | sfrom | slength |
+-------+------------+--------+----------------+-------+---------+
|     0 | 09-24-2018 | 943023 | RADIOLOGY      | 03PM  |       8 |
|     1 | 09-24-2018 | 891046 | SURGERY        | 07AM  |       8 |
|     2 | 09-24-2018 | 878397 | RADIOLOGY      | 11PM  |       8 |
|     3 | 09-24-2018 | 886190 | CARDIOLOGY     | 03PM  |       8 |
|     4 | 09-24-2018 | 878397 | RADIOLOGY      | 11PM  |       8 |
|     5 | 09-24-2018 | 891330 | SURGERY        | 03PM  |       8 |
|     6 | 09-24-2018 | 995561 | SURGERY        | 11PM  |       8 |
|     7 | 09-24-2018 | 967577 | MATERNITY      | 03PM  |       8 |
|     8 | 09-24-2018 | 913891 | PEDIATRICS     | 03PM  |       8 |
|     9 | 09-24-2018 | 939148 | EMERGENCY      | 07AM  |       8 |
|    10 | 09-24-2018 | 995636 | RADIOLOGY      | 11PM  |       8 |
|    11 | 09-24-2018 | 995561 | EMERGENCY      | 11PM  |       8 |
|    12 | 09-24-2018 | 967577 | SURGERY        | 07AM  |       8 |
|    13 | 09-24-2018 | 883069 | INTENSIVE CARE | 07AM  |       8 |
|    14 | 09-24-2018 | 951876 | ONCOLOGY       | 03PM  |       8 |
|    15 | 09-24-2018 | 884866 | CARDIOLOGY     | 07AM  |       8 |
|    16 | 09-24-2018 | 864425 | ONCOLOGY       | 03PM  |       8 |

员工表

+-------+--------+------------+-----------+---------+----------------+----------------+------+
| index | empid  | lastName   | firstName | emptype | cellphone      | homephone      | ftpt |
+-------+--------+------------+-----------+---------+----------------+----------------+------+
|     0 | 919675 | Cermak     | Vern      | LPN     | (138)7198-0862 | (655)2249-9926 | FT   |
|     1 | 906704 | Paille     | Lorilee   | NULL    | (858)1100-2722 | (377)1506-1986 | PT   |
|     2 | 900486 | Ober       | Shalanda  | NULL    | (210)1508-8132 | (820)1612-0197 | FT   |
|     3 | 883367 | Clyburn    | Jeffry    | LPN     | (420)7798-7220 | (476)7661-3070 | FT   |
|     4 | 953807 | Quarles    | Timmy     | LPN     | (513)2756-9892 

如果我必须计算每个部门每个员工类型和每个类次的总成本，我应该如何在 sql 中执行此操作？

Given that if a shift includes midnight I multiply my wage by 25% ?

我有一个示例输出如下，但不知道如何执行此操作？:

+------------+---------------+----------------+---------+
| DEPARTMENT | EMPLOYEE_TYPE |     SHIFT      |  COST   |
+------------+---------------+----------------+---------+
| Anesthesia | LPN           |    7 AM –3     | $24,567 |
| Anesthesia | LPN           |    3 PM –11PM  | $18,546 |
| Anesthesia | LPN           |    11 PM –7AM  | $22,874 |
| Anesthesia | NA            |    7 AM –3 PM  | $9,764  |
| Anesthesia | NA            |    3 PM –11 PM | $10,287 |
| Anesthesia | NA            |    11 PM –7 AM | $6, 875 |
| Anesthesia | RN            |    7 AM –3 PM  | $33,123 |
+------------+---------------+----------------+---------+

到目前为止，这是我的 sql 语句:但是我应该如何让它工作呢？编辑 1:

 Select 
      t1.dept,
      t2.emptype,
      t1.sfrom,
      t1.slength,
      SUM(t1.slength) as hours 
      CASE WHEN CAST(STR_TO_DATE(sfrom,'%h%p') + 
                     STR_TO_DATE(slength,'%H')) > STR_TO_DATE(23,'%H') 
      then (slength*10*1.25) else (slength*10) end as cost 
 from 
      schedule_2 as t1 
      Join (select 
                empid,
                emptype 
            from 
                employees_2) as t2 ON t1.empid=t2.empid 
 GROUP BY 
       dept,
       emptype,
       slength;

编辑2:

Select 
    t1.dept,
    t2.emptype,
    t1.sfrom,
    t1.slength,
    SUM(t1.slength) as hours
    CASE WHEN HOUR(STR_TO_DATE(sfrom,'%h%p') + 
                   STR_TO_DATE(slength,'%H')) > 23 
    then (slength*10*1.25) else (slength*10) 
from 
    schedule_2 as t1 
    Join (select 
               empid,
               emptype 
          from` 
               employees_2) as t2 ON t1.empid=t2.empid 
 GROUP BY 
      dept,
      emptype,
      slength;

我收到以下错误，但似乎无法弄清楚原因？

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASE WHEN HOUR(STR_TO_DATE(sfrom,'%h%p') + STR_TO_DATE(slength,'%H')) > 23 then ' at line 2

Answer 1

您可以在您的 sql 语句中为成本字段使用 case 子句。例如:

case when <<your clause for night shift>> then (slength*10)+(0.25*10*slength)
else (slength*10) end as cost

假设是每小时 10 美元。您可以根据业务规则设置夜类条件。