分组最大查询以获取最新日期的记录 [英] Groupwise maximum query for getting records for latest date

问题描述

我正在尝试建立一个仅基于日期获取一组记录中最新记录的查询。

I'm trying to build a query that only gets the latest record on a group of records based on date.

表的布局如下：

| date | category | action | label | label2 | count_today | count_total | period |

主键基于列 date ，类别，操作，标签， label2 ，期间。 日期的格式为 yyyy-mm-dd ，期间的值可以为 Day ，周，月。

The primary key is based on the columns date, category, action, label, label2, period. date has format yyyy-mm-dd and period can have the values Day, Week, month.

对于类别的每个唯一组合|动作|标签| label2 我需要具有最新日期的记录。

For each unique combination of category | action | label | label2 I need to have the record with the latest date.

我的第一次尝试是：

SELECT * FROM `statistic` 
WHERE 
 (action='total' OR action='' OR category='user')
 AND
 (period='day' 
   OR (period='week' AND DATEDIFF(now(), `date`) > 30)
   OR (period = 'Month' AND DATEDIFF(now(), `date`) > 7*26)
 )
GROUP BY category, action, label, label2
ORDER BY date DESC

此查询的问题是它在ORDER BY之前执行GROUP BY，导致返回错误的记录。

The problem with this query is that it does the GROUP BY before the ORDER BY, causing incorrect records to be returned.

搜索后，我发现我想要的被称为 group -wise maximum 查询。

After searching, I found that what I want is called a group-wise maximum query.

我的下一个尝试是：

SELECT s1.* FROM `statistic` AS s1
LEFT JOIN statistic AS s2 
ON 
 s1.category = s2.category
 AND s1.action = s2.action
 AND s1.label = s2.label
 AND s1.label2 = s2.label2
 AND s1.date > s2.date
WHERE 
   (s1.action='total' OR s1.action='' OR s1.category='user')
   AND
   (s1.period='day' 
   OR (s1.period='week' AND DATEDIFF(now(), s1.`date`) > 30)
   OR (s1.period = 'Month' AND DATEDIFF(now(), s1.`date`) > 7*26)
 )
GROUP BY category, action, label, label2

但是此查询也没有给我正确的结果（它看起来类似于第一个查询）。

But this query doesn't give me the correct results either (it looks similar to the first query).

任何知道如何获取所需的数据？

Any clue how I can get the data that I need?

推荐答案

您是对的，您想要逐组最大值，但是您可以通过将表与子查询联接来完成此操作查找每个组的最新日期：

You are right that you want the group-wise maximum, but you accomplish this by joining your table with a subquery that finds the latest date for each group:

SELECT * FROM statistic NATURAL JOIN (
  SELECT   category, action, label, label2, MAX(date) date
  FROM     statistic
  GROUP BY category, action, label, label2
) t

然后，如果仍然需要以下过滤器： / p>

And then, if the following filters are still required:

WHERE 
 (action='total' OR action='' OR category='user')
 AND
 (period='day' 
   OR (period='week' AND DATEDIFF(now(), `date`) > 30)
   OR (period = 'Month' AND DATEDIFF(now(), `date`) > 7*26)
 )

这篇关于分组最大查询以获取最新日期的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！