mysql使用group by多次连接同一个表
mysql使用group by多次连接同一个表
提问于 2018-05-28 18:05:20
我有表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`我们怎么能多次连接同一个表，但是有不同的日期时间范围，并且每个表都有单独的sum列呢？
回答 2
Stack Overflow用户
发布于 2018-05-28 18:08:47
尝试使用OR在同一连接表中添加所有不同的条件
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'
OR
`lastDay`.`time` between '2018-04-28 00:00:00' and '2018-05-28 00:00:00'
OR
`lastDay`.`time` between '2017-05-28 00:00:00' and '2018-05-28 00:00:00'
)
group by `products`.`id`Stack Overflow用户
发布于 2018-05-28 18:12:29
您可以通过单连接操作为不同的时间范围使用条件聚合
SELECT
p.id,
p.name,
SUM(CASE WHEN `lastDay`.`time` BETWEEN '2018-05-27 00:00:00' AND '2018-05-28 00:00:00' THEN lastDay.unitsSold ELSE 0 END) AS latDayUnitsSold,
SUM(CASE WHEN `lastDay`.`time` BETWEEN '2018-04-28 00:00:00' AND '2018-05-28 00:00:00' THEN lastDay.unitsSold ELSE 0 END) AS latMonthUnitsSold,
SUM(lastDay.unitsSold) AS latYearUnitsSold,
FROM `products` p
LEFT JOIN
`productStats` AS `lastDay`
ON p.`id` = `lastDay`.`productId`
AND `lastDay`.`time` BETWEEN '2017-05-28 00:00:00' AND '2018-05-28 00:00:00'
GROUP BY p.id, p.name页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50563855
复制相关文章
相似问题