php - 将行连接到上一个最近的日期行

Question

数据库数据:

id | account | date       | random_data
1  | 1       | 01/01/2013 | qw
2  | 2       | 05/01/2013 | er
3  | 2       | 09/01/2013 | ty
4  | 1       | 05/01/2013 | ui
5  | 2       | 11/01/2013 | op
6  | 1       | 12/01/2013 | as

您好，假设我想要从 05/01/2013 开始的记录 - 请注意，第一行的 prev_date 仍然显示早于 05/01 的日期，这意味着仍然需要搜索整个表格。

结果数据:

account | cur_date   | random_data | prev_date  | prev_rand_data
1       | 05/01/2013 | ui          | 01/01/2013 | qw
1       | 12/01/2013 | as          | 05/01/2013 | ui
2       | 05/01/2013 | er          | null       | null
2       | 09/01/2013 | ty          | 05/01/2013 | er
2       | 11/01/2013 | op          | 09/01/2013 | ty

所以我不确定我可以为此使用的最好、最优化的查询是什么。我不反对 php 解决方案，但不确定那会好多少。我考虑过的一些想法:

1. 在同一张表上进行某种联接 - 不确定如何

2. 选择子查询 -

   select date as cur_date
, (select max(date)
from table
where date < cur_date
group by account)
as prev_date... - 这似乎是非常密集的

3. session 变量 - 在每一行上设置一个 session 变量，这将是下一行的先前数据，例如

   select date as cur_date
, @prev_date as prev_date
, @prev_date:=date...

有没有人遇到过这样的问题，有好的解决方案吗？我的任何想法是否有可能在未来导致问题的正面负面影响？

Answer 1

我会结合使用 sql 和应用程序代码。由于我不是 php 程序员，所以我只会描述用于应用程序部分的逻辑。

首先是查询。

select account, date, random_data
from thetable
where date >= YourDateVariable

union

select account, date, random_data
from thetable join
(select account acc, max(date) maxdate
from thetable
where date <= YourDateVariable
group by account) x on account = acc and date = max(date)
where date <= YourDateVariable

order by account, date

对于应用程序代码，执行以下操作:

Set a variable called ThisAccount to 0.
Set a row counter variable to 0.
Create an empty 2D array
Start looping through your query results
Put the account value and random data into the first two columns
    of the next available row of the array
Compare the account value to the value of the ThisAccount variable.  
   If they are the same, get the previous date and random data from 
   the previous row in the array.
Set the ThisAccount variable to the current account value.
Increment your row counter variable
End of loop.