mysql计算一个月内数据,MySQL：从表中计算数据，偏移一个月

I am pretty new to SQL, so forgive me if this is a noob question. I am building a stored procedure that queries from a table that has data stored by month. Here is a simplified example of what the table looks like:

+------------+------------+------------+

| date | value_A | value_B |

+------------+------------+------------+

| 2014-01-01 | 100 | 200 |

| 2014-02-01 | 200 | 400 |

| 2014-03-01 | 300 | 600 |

| 2014-04-01 | 400 | 800 |

| 2014-05-01 | 500 | 1000 |

| 2014-06-01 | 600 | 1200 |

| 2014-07-01 | 700 | 1400 |

| ... | ... | ... |

+------------+------------+------------+

In the procedure, I need to calculate: (Value_A / Value_B). However, Value_B needs be lagged one month behind. So in the example above, the calculation for February 2014 would be: (Value_A of Feb 2014 / Value_B or Jan 2014) or (200 / 200). The calculation for March 2014 would then be: (Value_A of Mar 2014 / Value_B of Feb 2014) or (300 / 400), and so on and so forth. Hope that makes sense.

It wouldn't be too difficult to do if I was only querying a specific month since I could use a CASE statement in the procedure. But since I need to retrieve a year's worth of data grouped by month, I am a bit stumped.

Without the one month lag of Value_B, I have the SQL statement set up like:

SELECT ex.`date `, ex.value_A / ex.value_B AS calc

FROM example_table ex

WHERE ex.`date` BETWEEN '2014-03-01' AND '2015-02-01'

GROUP BY ex.`date`

I'm not sure where to go from here. I've experimented with different joins and unions but haven't had any luck. Do I need to query Value_A and Value_B separately with their own date range, then merge them together somehow for the calculation? Or am I making this much harder than it really is?

Thanks for your help.

解决方案SELECT t.date, t.value_A/t2.value_B AS calc

FROM table t JOIN table t2 ON t.date = DATE_ADD(t2.date, INTERVAL 1 MONTH)