获取行之间具有固定值差异的行并减少连续日期

Question

Structure of my table : ID(int) | NUMBER(int) | CREATED_AT(date)

If I have three numbers : 10, 11 and 9, Can I get all rows from my table with same ratio of NUMBER values with decreasing consecutive CREATED_AT dates?

Examples :

If my numbers are : 10,11,9 ... The rows may be :

NUMBER | CREATED_AT
50     | 2012-03-18
51     | 2012-03-17
49     | 2012-03-16

Result must be "50"

If my numbers are : 50,40,60 ... The rows may be :

NUMBER | CREATED_AT
100    | 2012-02-20
90     | 2012-02-19
110    | 2012-02-18

Result must be "100"

...

I wasted a half of day for this, but I still have no Idea where to start...

P.S : I have about 5 000 rows in my table.

UPDATE

I did it with a bit of php code and a lot of mysql queries. Script execution time : ~6 seconds. [Tested on godaddy deluxe hosting] =)

Answer 1

I haven't tested it myself, but you can try this:

SELECT a.*,b.*,c.*
FROM table_name a
INNER JOIN table_name b 
ON b.NUMBER = a.NUMBER + $range1 and DATEDIFF(b.CREATED_AT, a.CREATED_AT) = 1
INNER JOIN table_name c 
ON c.NUMBER = b.NUMBER + $range2 and DATEDIFF(c.CREATED_AT, b.CREATED_AT) = 1

$range1, $range2 are variables number1 - number2 and number2 - number3.