SQLite中如何基于指定行查询同水果的前序特定行

表结构与示例数据 #

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 |

需求 #

给定目标行（如id=6，对应日期5/16、水果Apple），需：

• 筛选出同一水果且日期≤目标日期的所有记录（示例中为id=1,4,6）
• 按日期从旧到新排序后，取目标行往前数shift位的记录：
  • shift=1返回id=4（目标行的前一条同水果记录）
  • shift=2返回id=1（目标行的前两条同水果记录）

原SQL的问题 #

你尝试的SQL存在两个关键问题：

1. SQLite不支持在WHERE子句中直接引用窗口函数（如ROW_NUMBER()）的结果
2. 排序方向和偏移逻辑未匹配需求，ORDER BY id DESC的行号分配方式无法直接对应你需要的偏移位置

正确实现方案 #

方案一：简洁版（利用LIMIT OFFSET） #

直接筛选符合条件的记录，按日期从新到旧排序，跳过shift行后取第一行，正好对应目标行往前数shift位的记录：

-- 替换id=6和shift为实际参数
WITH target AS (
    SELECT Fruit, Date FROM M1Bars WHERE id = 6
)
SELECT * FROM (
    SELECT m.*
    FROM M1Bars m
    JOIN target t 
      ON m.Fruit = t.Fruit 
      AND m.Date <= t.Date
    ORDER BY Date DESC, id DESC
)
LIMIT 1 OFFSET shift;

• 解释：按Date DESC排序后，第一行是目标行，OFFSET shift会跳过前shift行（包含目标行），取到的下一行就是需求结果。比如shift=1跳过目标行，取第二行id=4；shift=2跳过前两行，取第三行id=1。

方案二：基于行号的精确匹配 #

如果需要更明确的行号逻辑，可先给符合条件的记录按日期升序编号，再通过目标行的行号减去偏移量定位：

-- 替换id=6和shift为实际参数
WITH target AS (
    SELECT Fruit, Date, id FROM M1Bars WHERE id = 6
),
ranked_records AS (
    SELECT 
        m.*,
        ROW_NUMBER() OVER (ORDER BY Date ASC, id ASC) AS row_num
    FROM M1Bars m
    JOIN target t 
      ON m.Fruit = t.Fruit 
      AND m.Date <= t.Date
)
SELECT * FROM ranked_records
WHERE row_num = (
    SELECT row_num FROM ranked_records WHERE id = (SELECT id FROM target)
) - shift;

• 解释：先给同水果且日期≤目标日期的记录按从旧到新编号，目标行id=6的行号是3，3-1=2对应id=4，3-2=1对应id=1，完全匹配需求。

内容的提问来源于stack exchange，提问作者i_sniff_ket