mysql 每个派生表必须有自己的 DELETE 查询别名

Question

我找到了一个答案，但它指的是 SELECT 而不是 DELETE，我不确定如何在这种情况下实现给定的答案。

通过下面的查询，我想删除除最后 10 个最新值(由 id_lv 定义)之外的所有值，但仅限一个用户。

DELETE FROM last_viewed
  WHERE id_lv <= (
    SELECT id_lv
    FROM (
      SELECT id_lv
      FROM last_viewed
      WHERE user_id_lv = '$user_id'
      ORDER BY id_lv DESC
      LIMIT 1 OFFSET 10
    )
  )

当我运行它时，我得到每个派生表必须有自己的别名

Answer 1

您必须在 DELETE 语句的子查询使用的派生表中使用别名:

DELETE FROM last_viewed
  WHERE id_lv <= (
    SELECT id_lv
    FROM (
      SELECT id_lv
      FROM last_viewed
      WHERE user_id_lv = '$user_id'
      ORDER BY id_lv DESC
      LIMIT 1 OFFSET 10
    ) AS t -- You need an alias here
  )