我對 join 和 sum 列有問題。我的查詢是
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`
問題是第二個left join
uj5u.com熱心網友回復：
您應該首先在表中聚合worker_hours，worker_cashes然后加入workers聚合的結果集，以免由于多次連接而多次獲得同一行：
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';
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/465649.html
上一篇：如何在JavaScript中與兩個物件陣列進行內部連接？
下一篇：如何列印查詢陳述句