我有一张table products ( id , name , price ，…）和 productStats ( productId , time , unitsSold , unitsReserved , ...).
在 productStats 我们有每分钟的行和那一分钟的所有必要的聚合值。如果我们需要获得一个时期的统计数据，那么可以：

select 
    id,
    name,
    SUM(lastDay.unitsSold) as latDayUnitsSold,
from `products`

left join 
    `productStats` as `lastDay` 
    on `products`.`id` = `lastDay`.`productId` 
    and `lastDay`.`time` between '2018-05-27 00:00:00' and '2018-05-28 00:00:00'

group by `products`.`id`

但是，当我们要同时获取多个时段的聚合值时，遇到了错误（错误值）：

select 
    id,
    name,
    SUM(lastDay.unitsSold) as latDayUnitsSold,
    SUM(lastMonth.unitsSold) as latMonthUnitsSold,
    SUM(lastYear.unitsSold) as latYearUnitsSold,
from `products`

left join 
    `productStats` as `lastDay` 
    on `products`.`id` = `lastDay`.`productId` 
    and `lastDay`.`time` between '2018-05-27 00:00:00' and '2018-05-28 00:00:00'

left join 
    `productStats` as `lastMonth` 
    on `products`.`id` = `lastMonth`.`productId` 
    and `lastDay`.`time` between '2018-04-28 00:00:00' and '2018-05-28 00:00:00'

left join 
    `productStats` as `lastYear` 
    on `products`.`id` = `lastYear`.`productId` 
    and `lastDay`.`time` between '2017-05-28 00:00:00' and '2018-05-28 00:00:00'

group by `products`.`id`

我们怎样才能多次连接同一个表，但具有不同的日期时间范围，并且每个表都有单独的总和列？