SQL加入和总和3

Question

我的加入和总和列有问题。我的查询是

SELECT 
sum(IFNULL(`worker_hours`.`godziny`, 0)) as godziny,
sum(IFNULL(`worker_hours`.`wartosc`, 0)) as wartosc,
sum(IFNULL(`worker_cashes`.`kwota`, 0)) as kwota,
`workers`.*
FROM 
`workers`
LEFT join `worker_hours` on `worker_hours`.`pracownik` = `workers`.`id`
LEFT join `worker_cashes` on `worker_cashes`.`pracownik` = `workers`.`id`
WHERE `workers`.`id_user` = '3'
group by
  `workers`.`id`

结果sum *2我的查询，我做错了什么？在我有这个查询中很好：

SELECT 
sum(IFNULL(`worker_hours`.`godziny`, 0)) as godziny,
sum(IFNULL(`worker_hours`.`wartosc`, 0)) as wartosc,
`workers`.*
FROM 
`workers`
left join `worker_hours` on `worker_hours`.`pracownik` = `workers`.`id`
WHERE `workers`.`id_user` = '3'
group by
  `workers`.`id`

​

Answer 1

您应该在表中首先汇总worker_hours和worker_cashes，然后加入workser到聚集的结果集，以免您获得相同的相同由于多个连接，行多次：

SELECT w.*,
       COALESCE(h.godziny, 0) AS godziny,
       COALESCE(h.wartosc, 0) AS wartosc,
       COALESCE(c.kwota, 0) AS kwota
FROM workers AS w
LEFT JOIN (
  SELECT pracownik,
         SUM(godziny) AS godziny,
         SUM(wartosc) AS wartosc
  FROM worker_hours 
  GROUP BY pracownik
) AS h ON h.pracownik = w.id
LEFT JOIN (
  SELECT pracownik,
         SUM(kwota) AS kwota
  FROM worker_cashes  
  GROUP BY pracownik
) AS c ON c.pracownik = w.id
WHERE w.id_user = '3';