在数据库查询中返回我的选择参数周围的行（“周围行”）

发布于 2024-12-19 22:11:49 字数 864 浏览 1 评论 0原文

我正在查询 Android 中的数据库。该表是带有值的普通表，没有什么特别的。

我需要什么：返回给定时间戳之前和之后发生的两个事件。

示例：假设我有下表，我的选择是 1332200003002：

_id |    Time       | Value
   ...    ...
n   | 1332200002000 | 145
n+1 | 1332200003001 | 98 (this is <= selection)
   (1332200003002 is here, between those two)
n+2 | 1332200004000 | 90 (this is > selection)
n+3 | 1332200005000 | 100
n+4 | 1332200005001 | 280

那么，如果我的选择是 1332200003001 或 1332200003002 ...我希望返回的行是n+1并且n+2，这样我就可以看到 Value 从 98 变为 90。

我使用的是一个 CursorLoader，因此它必须最好适合其通常的调用。

我的代码大小谢谢！

作为旁注，我可以猜测BETWEEN的安全值（它正在已经工作），然后迭代Java 中剩余的几行 Cursor 行可以精确定位我需要的两行。然而，在我看来，这是一个非常普遍的需求，因此就有了这个问题。在 Java 中使用我们需要使用游标进行的所有常见缓冲测试似乎很浪费。

原文

I'm querying a database in Android. The table is your ordinary table with values, nothing special.

What I need: return the two events that happened before and after the given timestamp.

Example: let's suppose I have the table below, and my selection is 1332200003002:

_id |    Time       | Value
   ...    ...
n   | 1332200002000 | 145
n+1 | 1332200003001 | 98 (this is <= selection)
   (1332200003002 is here, between those two)
n+2 | 1332200004000 | 90 (this is > selection)
n+3 | 1332200005000 | 100
n+4 | 1332200005001 | 280

So, if my selection is 1332200003001, or 1332200003002... I'd want the returned rows to be n+1 and n+2, so that I can see that the Value went from 98 to 90.

What I'm using is a CursorLoader, so it must preferably fit into its usual call.

My code size thanks you!

As a side note, I can guess safe values for BETWEEN (it IS working already), and then iterate the few remaining Cursor rows in Java to pinpoint the two rows that I need. However, this seems to me like a very common need, hence the question. Seems a waste to do it in Java with all those usual bumper tests we need to do with a Cursor.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

北斗星光 2024-12-26 22:11:49

SELECT *
FROM myTable LIMIT 2
OFFSET
  (SELECT _id
   FROM myTable
   WHERE time<=1332200003002
   ORDER BY time DESC LIMIT 1) - 1;

作用：

从表中选择 2 个条目。第一个条目的偏移量选择如下：

选择time <= 1332200003002的最新行，并计算其相对于第一行的偏移量。

如果您的 _id 值从 1 而不是 0 开始，则需要末尾的 -1。（根据需要更改此值以转换您的 _id值转换为从零开始的偏移量。）

SELECT *
FROM myTable LIMIT 2
OFFSET
  (SELECT _id
   FROM myTable
   WHERE time<=1332200003002
   ORDER BY time DESC LIMIT 1) - 1;

What this does:

Select 2 entries from the table. The offset of the first entry is selected as follows:

Choose the latest row where time <= 1332200003002, and calculate its offset from the first row.

The -1 at the end is needed if your _id values start at 1 rather than 0. (Change this value as needed to convert your _id values into zero-based offsets.)

回复收藏 0 原文

~没有更多了~