MySQL получает результаты объединений или нулей

Немного заржавел на моем запросе ... и я уже некоторое время пытаюсь получить необходимые результаты. Сначала попробовал поискать, но, возможно, я не ищу правильные термины.

У меня есть стол с людьми и стол с часами. Я пытаюсь получить результаты, как это:

name    mon     tue     wed     thu     fri     total
Bob     15      8.5     9.25    8.75    15      56.5
Joe     10.5    0       0       0       0       10.5

Это запрос у меня в настоящее время:

SELECT e.name,m.mon,t.tue,w.wed,th.thu,f.fri,sum(m.mon+t.tue+w.wed+th.thu+f.fri) as total 
FROM people e 
JOIN (SELECT uid, round(((TIME_TO_SEC(tout) - TIME_TO_SEC(tin))/60)/60,2) AS mon FROM vhours WHERE DAYNAME(wdate)='Monday' AND YEARWEEK(wdate)=YEARWEEK(NOW())) as m ON m.uid=e.id 
JOIN (SELECT uid, round(((TIME_TO_SEC(tout) - TIME_TO_SEC(tin))/60)/60,2) AS tue FROM vhours WHERE DAYNAME(wdate)='Tuesday' AND YEARWEEK(wdate)=YEARWEEK(NOW())) as t ON t.uid=e.id 
JOIN (SELECT uid, round(((TIME_TO_SEC(tout) - TIME_TO_SEC(tin))/60)/60,2) AS wed FROM vhours WHERE DAYNAME(wdate)='Wednesday' AND YEARWEEK(wdate)=YEARWEEK(NOW())) as w ON w.uid=e.id 
JOIN (SELECT uid, round(((TIME_TO_SEC(tout) - TIME_TO_SEC(tin))/60)/60,2) AS thu FROM vhours WHERE DAYNAME(wdate)='Thursday' AND YEARWEEK(wdate)=YEARWEEK(NOW())) as th ON th.uid=e.id 
JOIN (SELECT uid, round(((TIME_TO_SEC(tout) - TIME_TO_SEC(tin))/60)/60,2) AS fri FROM vhours WHERE DAYNAME(wdate)='Friday' AND YEARWEEK(wdate)=YEARWEEK(NOW())) as f ON f.uid=e.id

Однако запрос даст результат, только если у человека есть часы на каждый день. Если у них нет часов каждый день, их часы вообще не отображаются.

Вот SQL Fiddle: http://sqlfiddle.com/#!9/4ac3cd/1

Всего 2 ответа


Вам нужно только 1 объединение таблиц и условное агрегирование:

SELECT p.name,
  COALESCE(SUM(CASE WHEN DAYNAME(v.wdate)='Monday' THEN round(((TIME_TO_SEC(v.tout) - TIME_TO_SEC(v.tin))/60)/60,2) END), 0) mon,
  COALESCE(SUM(CASE WHEN DAYNAME(v.wdate)='Tuesday' THEN round(((TIME_TO_SEC(v.tout) - TIME_TO_SEC(v.tin))/60)/60,2) END), 0) tue,
  COALESCE(SUM(CASE WHEN DAYNAME(v.wdate)='Wednesday' THEN round(((TIME_TO_SEC(v.tout) - TIME_TO_SEC(v.tin))/60)/60,2) END), 0) wed,
  COALESCE(SUM(CASE WHEN DAYNAME(v.wdate)='Thursday' THEN round(((TIME_TO_SEC(v.tout) - TIME_TO_SEC(v.tin))/60)/60,2) END), 0) thu,
  COALESCE(SUM(CASE WHEN DAYNAME(v.wdate)='Friday' THEN round(((TIME_TO_SEC(v.tout) - TIME_TO_SEC(v.tin))/60)/60,2) END), 0) frid,
  COALESCE(SUM(round(((TIME_TO_SEC(v.tout) - TIME_TO_SEC(v.tin))/60)/60,2)), 0) Total
FROM people p LEFT JOIN vhours v
ON v.uid = p.id AND YEARWEEK(v.wdate)=YEARWEEK(NOW()) AND DAYOFWEEK(v.wdate) BETWEEN 2 AND 6   
GROUP BY p.id, p.name

Смотрите демо .
Или даже лучше:

SELECT p.name,
  COALESCE(SUM(CASE WHEN v.day = 'Monday' THEN v.hours END), 0) mon,
  COALESCE(SUM(CASE WHEN v.day = 'Tuesday' THEN v.hours END), 0) tue,
  COALESCE(SUM(CASE WHEN v.day = 'Wednesday' THEN v.hours END), 0) wed,
  COALESCE(SUM(CASE WHEN v.day = 'Thursday' THEN v.hours END), 0) thu,
  COALESCE(SUM(CASE WHEN v.day = 'Friday' THEN v.hours END), 0) fri,
  COALESCE(SUM(v.hours), 0) Total
FROM people p LEFT JOIN (
  SELECT uid, DAYNAME(wdate) day,
    ROUND(TIMESTAMPDIFF(SECOND, tin, tout) / 3600, 2) hours
  FROM vhours 
  WHERE YEARWEEK(wdate) = YEARWEEK(NOW()) AND DAYOFWEEK(wdate) BETWEEN 2 AND 6
) v
ON v.uid = p.id 
GROUP BY p.id, p.name

Смотрите демо .
Результаты:

| name | mon | tue | wed  | thu  | fri  | Total |
| ---- | --- | --- | ---- | ---- | ---- | ----- |
| Bob  | 15  | 8.5 | 9.25 | 8.75 | 15   | 56.5  |
| Joe  | 0   | 11  | 0    | 0    | 0    | 11    |
| Dan  | 0   | 0   | 0    | 10.5 | 0    | 10.5  |
| Carl | 0   | 0   | 0    | 0    | 0    | 0     |

Используйте LEFT JOIN вместо JOIN . Это вернет нулевые значения для всех несоответствующих строк, вы можете использовать IFNULL() чтобы преобразовать их в 0 .

Не используйте SUM() . Это для суммирования по строкам, вам это не нужно, когда вы добавляете столбцы вместе. Это делается с помощью + со всеми именами столбцов.

SELECT e.name,IFNULL(m.mon, 0) mon,IFNULL(t.tue, 0) tue, IFNULL(w.wed, 0) wed, IFNULL(th.thu, 0) thu, IFNULL(f.fri, 0) fri,
      IFNULL(m.mon, 0)+IFNULL(t.tue, 0)+IFNULL(w.wed, 0)+IFNULL(th.thu, 0)+IFNULL(f.fri, 0) as total 
FROM people e 
LEFT JOIN (SELECT uid, round(((TIME_TO_SEC(tout) - TIME_TO_SEC(tin))/60)/60,2) AS mon FROM vhours WHERE DAYNAME(wdate)='Monday' AND YEARWEEK(wdate)=YEARWEEK(NOW())) as m ON m.uid=e.id 
LEFT JOIN (SELECT uid, round(((TIME_TO_SEC(tout) - TIME_TO_SEC(tin))/60)/60,2) AS tue FROM vhours WHERE DAYNAME(wdate)='Tuesday' AND YEARWEEK(wdate)=YEARWEEK(NOW())) as t ON t.uid=e.id 
LEFT JOIN (SELECT uid, round(((TIME_TO_SEC(tout) - TIME_TO_SEC(tin))/60)/60,2) AS wed FROM vhours WHERE DAYNAME(wdate)='Wednesday' AND YEARWEEK(wdate)=YEARWEEK(NOW())) as w ON w.uid=e.id 
LEFT JOIN (SELECT uid, round(((TIME_TO_SEC(tout) - TIME_TO_SEC(tin))/60)/60,2) AS thu FROM vhours WHERE DAYNAME(wdate)='Thursday' AND YEARWEEK(wdate)=YEARWEEK(NOW())) as th ON th.uid=e.id 
LEFT JOIN (SELECT uid, round(((TIME_TO_SEC(tout) - TIME_TO_SEC(tin))/60)/60,2) AS fri FROM vhours WHERE DAYNAME(wdate)='Friday' AND YEARWEEK(wdate)=YEARWEEK(NOW())) as f ON f.uid=e.id

DEMO


Есть идеи?

10000