Dość trochę zardzewiały na moim zapytaniu ... i od jakiegoś czasu próbuję uzyskać potrzebne wyniki. Najpierw spróbowałem wyszukać, ale być może nie wyszukuję odpowiednich terminów.

Mam stolik dla ludzi i stół godzinowy. Próbuję uzyskać takie wyniki:

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

Oto zapytanie, które obecnie mam:

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

Jednak zapytanie da wynik tylko wtedy, gdy osoba ma godziny na każdy dzień. Jeśli nie mają godzin pracy każdego dnia, ich godziny w ogóle się nie pojawiają.

Oto SQL Fiddle: http://sqlfiddle.com/#!9/4ac3cd/1

0
Chris 27 marzec 2020, 01:59

2 odpowiedzi

Najlepsza odpowiedź

Potrzebujesz tylko 1 sprzężenia tabel i agregacji warunkowej:

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

Zobacz demo.
Albo jeszcze lepiej:

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

Zobacz demo.
Wyniki:

| 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     |
2
forpas 27 marzec 2020, 07:46

Użyj LEFT JOIN zamiast JOIN. To zwróci wartości null dla wszystkich niepasujących wierszy, możesz użyć IFNULL(), aby przekonwertować je na 0.

Nie używaj SUM(). To służy do sumowania w wierszach, nie jest to potrzebne, gdy dodajesz kolumny. Robi się to po prostu używając + ze wszystkimi nazwami kolumn.

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

PRÓBNY

0
Barmar 26 marzec 2020, 23:21