获取最近的记录到按类型分组的特定日期 [英] Get nearest records to specific date grouped by type

问题描述

对不起，我不能更具体，但我不知道如何描述我的问题在标题中..分别我不知道如何搜索类似的问题。

这就是交易。我有这张表的记录：

正如您所看到的，我拥有不同类型的不同月份的数据。我的目标是获得按类型分组的最后输入金额，这应该由日期来限制。

例如：

如果我指定日期2011-03-01，则查询应返回3个记录，其金额为10,20和30，因为对于类型= 1，最近插入的记录为'2011-03 -01'的日期为2011-01-01，数量为10.对于类型= 2，与2011-03-01最接近的日期是2011-02-01，数量为20.而对于类型= 3，最接近'2011 -03-01'是'2011-03-01'，因此它返回的金额= 30。

那么如何构成MySQL查询？

解决方案

现在我只能想到一个子选择版本：

  SELECT类型，数量
 FROM表格t 
 WHERE dat =（
 SELECT MAX（dat）
 FROM table 
 WHERE type = t.type 
 AND dat< ='2011-03-01'
）
  

I'm sorry I can't be more specific, but I don't know how to describe my problem in the title.. respectively I don't know how to search for similar question.

So here is the deal. I have this table with records:

As you can see, I have data, entered on different months with different type. My goal is to get the last entered amount grouped by type and this should be limited by date.

For example:

If I specify a date 2011-03-01, the query should return 3 records with amount - 10, 20 and 30, because for type=1 the nearest inserted record to '2011-03-01' is on date 2011-01-01 with amount 10. For type=2 the nearest date to '2011-03-01' is 2011-02-01 with amount 20. And for type=3 the nearest to '2011-03-01' is the '2011-03-01' so it returns amount=30.

So how to form the MySQL query?

解决方案

Right now I can only think of a subselect version:

SELECT type, amount
FROM table AS t
WHERE dat = (
    SELECT MAX(dat)
    FROM table
    WHERE type=t.type
        AND dat <= '2011-03-01'
)

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