按日期排序并允许重复日期时，在MySQL中获取前后记录

I've tried looking up other help for this problem but I'm just not getting it. Suppose I have a table that looks like the one below.

我已经尝试过寻找其他的帮助来解决这个问题，但是我还是得不到。假设我有一个像下面这个的表格。

+----+--------------+------------+
| id | date_col     | label      |
+----+--------------+------------+
| 1  | 2010-09-07   | Record 1   |
| 2  | 2010-09-03   | Record 2   |
| 3  | 2010-08-23   | Record 3   |
| 4  | 2010-08-23   | Record 4   |
| 5  | 2010-08-23   | Record 5   |
| 6  | 2010-08-12   | Record 6   |
| 7  | 2010-08-06   | Record 7   |
| 8  | 2010-08-06   | Record 8   |
| 9  | 2010-08-02   | Record 9   |
| 10 | 2010-08-01   | Record 10  |
+----+--------------+------------+

When queried, I order these records according to date_col and use id (or really any other arbitrary column) to help ordering with duplicate dates.

当查询时，我根据date_col对这些记录进行排序，并使用id(或任何其他任意列)帮助对重复日期进行排序。

mysql_query("SELECT * FROM table ORDER BY date_col DESC, id DESC");

However, when I query only one of these records at a time, I want to have a previous and a next button to navigate to the next or previous record. My problem is that date_col allows duplicate values and so, for example, the query below does not work for me when determining the next record in sequence. (assume that this_date is the date_col value and this_id is the id value for the current record we are looking at)

然而，当我一次只查询其中的一条记录时，我希望有一个上一个和一个下一个按钮来导航到下一个或上一个记录。我的问题是，date_col允许重复的值，因此，例如，下面的查询在我确定顺序的下一条记录时不起作用。(假设此日期为date_col值，而this_id为当前记录的id值)

mysql_query("SELECT id FROM table WHERE date_col > this_date ORDER BY date_col DESC, id DESC LIMIT 1");

Even this wouldn't work for me:

即使这样对我也不起作用:

mysql_query("SELECT id FROM table WHERE date_col > this_date AND NOT id=this_id ORDER BY date_col DESC, id DESC LIMIT 1");

So what I'm going for is something like this - If I'm looking at the record with id #4, since its being ordered by date_col DESC id DESC, the previous record should be id #5 and the next record should be id #3, but I'm not getting these results at all.

我要的是这样的——如果我在看记录id # 4,被date_col DESC id命令DESC以来,纪录应该id # 5和下一个记录应该是id # 3,但我没有得到这些结果。

Can someone explain how to make this work properly? Any help is much appreciated.

有人能解释一下如何使这个工作正常进行吗?非常感谢您的帮助。

2 个解决方案

#1

If you're going to have workable PREV and NEXT buttons in your view, you're going to need a solid conceptual foundation for record ordering in your data model. As you have noticed, your date_col doesn't do the trick because it permits duplicates.

如果要在视图中使用可使用的PREV和NEXT按钮，则需要在数据模型中为记录排序打下坚实的概念基础。正如您所注意到的，date_col没有这么做，因为它允许复制。

It seems like your concept of NEXT(id) means "the row with the smallest ID number whose ID number is greater than the current id number and whose date is greater than the current row's date." That's OK, but it has a flaw: PREV(NEXT(id)) isn't necessarily equal to id in all cases. This may drive your users around the bend, and may drive your program logic into some real kludges to get things to work.

似乎您的NEXT(id)的概念是“id号最小、id号大于当前id号、日期大于当前行的日期的行”。这没问题，但它有一个缺陷:PREV(NEXT(id)不一定在所有情况下都等于id。这可能会驱使你的用户转弯抹角，也可能会驱使你的程序逻辑变成一些真正的拼凑物来让东西工作。

Why not simply use the serial number?

为什么不直接使用序列号呢?

To get the "next" row, either do

要得到“下一个”行，你可以这样做

SELECT id FROM table WHERE id = current_id +1

or, if your ID numbers are not guaranteed to be contiguous, do

或者，如果您的ID号不能保证是连续的，那么请这样做

SELECT min(id) AS id FROM table WHERE id > current_id

If you must show the next date instead of the next row with your next button, then do

如果你必须用next按钮显示下一个日期而不是下一行，那么就这样做

SELECT id, date_col
  FROM table 
 WHERE id = (   
  SELECT min(id) AS id
    FROM table 
   WHERE date_col > this_date
)

But if you do that be sure to create an index for date_col. There are equivalent versions of all these queries to use for your PREV button.

但是如果您这样做的话，一定要为date_col创建一个索引。对于PREV按钮，可以使用所有这些查询的等效版本。

#2

If you can handle the initial data hit (recordset isn't huge) a very simple workout would be to query ALL into an array, order as necessary via array functions, then grab data based on the array key which is also your ordering. Again, the limitation on this example is that if you're going to be dealing with tons of data, it's probably not the best option.

如果可以处理初始数据命中(recordset并不大)，一个非常简单的方法是将所有数据查询到一个数组中，根据需要通过数组函数进行排序，然后根据数组键(也是您的排序)获取数据。同样，这个例子的局限性是如果你要处理大量的数据，它可能不是最好的选择。

#1