时间戳

首页 > 解决方案 > 我被逻辑困住了，也许我做错了 SQL

mysql - 我被逻辑困住了，也许我做错了 SQL

问题描述

到目前为止我尝试过的查询

select sum(g1.datacount) from
(select DAte(DATE) as 'newdate', COUNT(RIDE_ID) as 'datacount' from 
ride_Data
where date(date) between (now() - interval 90 day) and now()
group by date(date)
order by date(date) desc) as g1
where g1.newdate between (now() - interval 7 day) and now()

上述查询的简短版本仅用于解释。

select sum(g1.datacount) from g1
where g1.newdate between (now() - interval 7 day) and now()

我想要实现的目标是 我想对列数据计数的最后 7 天求和，如您所见，我已经正确完成了该部分，但现在有任何问题，但在实际情况下，我希望在 7 天组中对最后 90 天求和，我不知道如何以这种方式分组？

有什么可以按我最近的 7 条记录结果分组的吗

标签： mysqlsql

解决方案

如果您想与现在进行比较，那么您可以使用带有 DIV 7 的 DATEDIFF 进行分组。

SELECT ((DATEDIFF(t.newdate, NOW())) DIV 7) AS WeekDiff,
-- MIN(t.newdate) AS MinDate,
-- MAX(t.newdate) AS MaxDate,
 COUNT(t.RIDE_ID) as TotalRides
FROM ride_Data t
WHERE t.newdate BETWEEN (NOW() - interval 90 day) AND NOW()
GROUP BY WeekDiff
ORDER BY WeekDiff DESC;

或者也许只是在一周内分组。 

SELECT YEAR(t.newdate)*100+WEEK(t.newdate, 3) AS WeekNr,
COUNT(t.RIDE_ID) as TotalRides
FROM ride_Data t
WHERE t.newdate BETWEEN (NOW() - interval 90 day) AND NOW()
GROUP BY WeekNr
ORDER BY WeekNr DESC;

推荐阅读