为子查询SQLite建立索引

lnxxn5zx 于 2023-03-08 发布在 SQLite

关注(0)|答案(2)|浏览(135)

我有一张日期表和一个我在那个日期吃的水果。一个水果不能在一个日期出现超过一次。

id | Fruit | Date |
--------------------
 1 | Apple | 5/12 |
 2 | Mango | 5/12 |
 3 | Grape | 5/13 |
 4 | Apple | 5/14 |
 5 | Grape | 5/14 |
 6 | Apple | 5/16 |
 7 | Apple | 5/17 |

给定一个特定的日期和一个水果（因此是一个特定的行），我试图找到日期在给定日期之前的行，这些行也有相同的水果......但有该行子集的特定索引。

SELECT * FROM
(
    SELECT * FROM M1Bars WHERE Date <= 5/16 AND Fruit = 'Apple'
)
WHERE ROW_NUMBER() OVER(ORDER BY id DEC)=shift;

假设给定行为6。
在内部选择中，我过滤掉了第6行之后所有没有Apple的行，所以我应该留下行[1，4，6]。
如果shift = 1，我需要第4行。
如果shift = 2，我需要第1行。
这就是我试图用外部选择来做的。我如何在sqlite中做到这一点？
编辑：还假设表格按日期排序！

sqlite

来源：https://stackoverflow.com/questions/75659628/indexing-a-subquery-sqlite

2条答案

按热度按时间

lqfhib0f1#

SQLite没有DATE数据类型，因此要使其工作，您的日期应该是数字或字符串可排序格式，例如20220516（YYYYMMDD）。
那么对于您的查询，左连接就可以完成这项工作

select  t1.Fruit, t1.id, t1.Date, t2.id, t2.Date
from M1Bars t1 left join M1bars t2 on t2.Fruit=t1.Fruit and t2.Date < t1.Date

这并不是完整的解决方案，但是可以按照这一行添加行号和基于行号之间移动的附加连接条件。

赞(0）回复(0）举报 2023-03-08

ubbxdtey2#

首先，必须将日期格式更改为SQLite的唯一有效文本格式YYYY-MM-DD，以便日期具有可比性。
不需要窗口函数，甚至不需要子查询。
可以在OFFSET子句中应用所需的条件：

SELECT * 
FROM M1Bars 
WHERE Date <= '2022-05-16' AND Fruit = 'Apple'
ORDER BY Date DESC
LIMIT 1 OFFSET <shift>; -- change <shift> with the value that you want

请参见demo。

赞(0）回复(0）举报 2023-03-08

我来回答

为子查询SQLite建立索引

2条答案

相关问题

热门标签

最新问答