mysql - 根据其他表减去值

Question

--work--
day    hours 
 4       8
 5       9
 6       8


 --crew--
employee     day
   2          4
   3          4
   6          4  
   2          5
   3          5
   4          5

--break--
hours    day    employee
  1       4        2
  .5      5        3

这是我的 sql 查询:

 SELECT w.hours, w.hours-b.hours,  c.employee
 FROM work w
 LEFT JOIN crew c ON w.day = c.day
 LEFT JOIN break b ON w.day = b.day
 WHERE w.day = 4

这是从每个员工身上减去 1 小时

我希望仅当该员工休息时才减去 break.hours。如果他没有，我只想要 work.hours。

Answer 1

COALESCE 就是您所需要的。

SELECT  a.employee,
        a.day,
        b.hours - COALESCE(c.hours,0) totalHours
FROM    crew a
        INNER JOIN work b
            ON a.day = b.day
        LEFT JOIN `break` c
            ON a.employee = c.employee AND
                a.day = c.day
-- WHERE    b.day = 4

• SQLFiddle Demo Link
• SQLFiddle Demo Link (with WHERE clause)