如何在SQLite中利用Row_Number()删除无主键表的指定行？

问题场景 #

作为SQL新手，我需要在无主键的SavedCarts表中删除指定行（用于学习研究）。现有表结构如下：

Users表 #

SavedCarts表（无主键） #

目标是删除SavedCarts中用户1的第3行数据。我已经能用ROW_NUMBER()查询到目标行，但尝试删除时全部失败，试过多种语句都无法实现，且尝试CTE时报错[SQLITE_ERROR] SQL error or missing database (no such table: CTE)，想知道不用CTE的实现方法。

已成功的查询语句 #

查询用户1的所有购物车内容（带行号） #

SELECT ROW_NUMBER() OVER ( PARTITION BY user) RowNum, 
cart_content 
FROM SavedCarts 
WHERE user = 1;

查询用户1的第3行购物车内容 #

SELECT * 
FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user) RowNum FROM SavedCarts ) 
WHERE user = 1 AND RowNum = 3;

尝试过的失败删除语句 #

1. 此语句会删除所有匹配user的行，而非目标行

DELETE FROM SavedCarts 
WHERE (user) IN (
   SELECT user FROM (
      SELECT user, ROW_NUMBER() OVER(PARTITION BY user) AS rownum 
      FROM SavedCarts
   ) 
   WHERE user = ? AND rownum = ?
);

2. 此语句会删除用户1的所有行

DELETE FROM SavedCarts 
WHERE user IN (
   SELECT * FROM (
      SELECT *, ROW_NUMBER() OVER(PARTITION BY user) AS rownum 
      FROM SavedCarts
   ) 
   WHERE rownum = ?
);
-- 执行后会删除用户1的所有内容

3. SQLite不支持在WHERE子句中直接使用窗口函数

DELETE FROM SavedCarts 
WHERE user = ? AND ROW_NUMBER() OVER(PARTITION BY user) = ?;

关于CTE的说明 #

SQLite其实支持CTE，你报错是因为语法错误，但既然需要不用CTE的方案，下面提供两种可行方法：

不用CTE的实现方案 #

方案1：通过字段组合匹配删除 #

假设同一用户的cart_content唯一，可通过匹配user和cart_content定位目标行：

DELETE FROM SavedCarts
WHERE user = ? AND cart_content = (
    SELECT cart_content
    FROM (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY user) AS rownum
        FROM SavedCarts
        WHERE user = ?
    ) AS sub
    WHERE rownum = ?
);

如果cart_content可能重复，可同时匹配所有字段：

DELETE FROM SavedCarts
WHERE (user, cart_content) IN (
    SELECT user, cart_content
    FROM (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY user) AS rownum
        FROM SavedCarts
        WHERE user = ?
    ) AS sub
    WHERE rownum = ?
);

方案2：利用SQLite隐式ROWID（推荐） #

SQLite会为每个表自动分配唯一的ROWID（除非表用WITHOUT ROWID创建），通过ROWID可精准定位单行：

DELETE FROM SavedCarts
WHERE ROWID = (
    SELECT ROWID
    FROM (
        SELECT ROWID, ROW_NUMBER() OVER(PARTITION BY user) AS rownum
        FROM SavedCarts
        WHERE user = ?
    ) AS sub
    WHERE rownum = ?
);

这个方法不受字段重复影响，能准确删除目标行。

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