Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

mysql - Get nearest records to specific date grouped by type

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:

sample database table

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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'
)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...