MySQL获取联接或零的结果

 收藏

我的queryfu有点生疏...并且一段时间以来,我一直在尝试获取所需的结果。尝试过先搜索,但是也许我没有搜索正确的术语。

我有一个人桌和一个小时表。我试图得到这样的结果:

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

但是,仅当某人每天有几个小时时,查询才会产生结果。如果他们每天没有时间,那么他们的时间根本不会显示。

Here is an SQL Fiddle: http://sqlfiddle.com/#!9/4ac3cd/1

回复
  • Use LEFT JOIN instead of JOIN. This will return null values for all the non-matching rows, you can use IFNULL() to convert them to 0.

    Don't use SUM(). That's for summing across rows, you don't need it when you're adding columns together. That's done just by using + with all the column names.

    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

  • 您只需要1个表联接和条件聚合:

    SELECT p.name,
      COALESCE(MAX(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(MAX(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(MAX(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(MAX(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(MAX(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
    

    See the demo.