MySQL根据类型分类到特定日期的最近记录 [英] MySQL get nearest records to specific date grouped by type
问题描述
所以这里是交易。我有这个表的记录:
如您所见,我有不同类型的不同月份的数据。我的目标是得到最后输入的数量按类型分组,这应该受到日期的限制。
例如:
如果我指定日期2011-03-01,查询应返回3个记录,金额为10,20和30,因为type = 1最近插入的记录为2011-03 -01'的日期是2011-01-01,金额为10.对于type = 2,2011-03-01的最近日期为2011-02-01,金额为20.而对于type = 3,最接近'2011 -03-01'是'2011-03-01',所以它返回数量= 30。
那么如何形成MySQL查询?任何帮助将不胜感激。
提前感谢
现在我只能想到一个子选择版本:
SELECT类型,金额
FROM表AS 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? Any help will be appreciated.
Thanks in advance.
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'
)
这篇关于MySQL根据类型分类到特定日期的最近记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!