使用限制和偏移删除行不使用子句

发布于 2025-02-07 02:08:35 字数 598 浏览 4 评论 0原文

我想用偏移删除行，因此我被迫使用嵌套查询，因为它在原始删除子句中不支持。

我知道这将有效（ID是主要键）：

DELETE FROM customers
WHERE ID IN (
    SELECT ID
    FROM customers
    WHERE register_date > '2012-01-01'
    ORDER BY register_date ASC
    LIMIT 5, 10
);

但是，当我获得错误

This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'.

服务器版本时，这是我的版本中不支持的：10.4.22-mariaDB

我该怎么做才能达到与上面支持的相同结果版本。

CREATE TABLE customers (
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL,
REGISTER_DATE DATETIME NOT NULL
);

原文

I want to delete rows with an offset, so I am forced to use a nested query since its not support in the raw DELETE clause.

I know this would worked (ID is the primary key):

DELETE FROM customers
WHERE ID IN (
    SELECT ID
    FROM customers
    WHERE register_date > '2012-01-01'
    ORDER BY register_date ASC
    LIMIT 5, 10
);

However, this is unsupported in my version as I get the error

This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'.

Server version: 10.4.22-MariaDB

What can I do to achieve the same result as above that is supported in my version.

CREATE TABLE customers (
ID INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(32) NOT NULL,
REGISTER_DATE DATETIME NOT NULL
);

分享到QQ

分享到微博

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

发布评论

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

辞取 2025-02-14 02:08:35

如果我没有错过一些简单的删除，即可完成工作...

delete customers
from (select *
      from customers
      WHERE register_date > '2012-01-01'
      order by register_date asc
      limit 5, 2) customers2 
join customers on customers.id = customers2.id

If I did not miss something a simple delete with join will do the job...

delete customers
from (select *
      from customers
      WHERE register_date > '2012-01-01'
      order by register_date asc
      limit 5, 2) customers2 
join customers on customers.id = customers2.id

Here is a demo for your version of MariaDB

回复收藏 0 原文

回忆凄美了谁 2025-02-14 02:08:35

将表加入使用row_number（）窗口函数对行进行排序并过滤要删除的行：

DELETE c
FROM customers c
INNER JOIN (
  SELECT *, ROW_NUMBER() OVER (ORDER BY register_date) rn
  FROM customers
  WHERE register_date > '2012-01-01'
) t ON t.ID = c.ID
WHERE t.rn > 5 AND t.rn <= 15; -- get 10 rows with row numbers 6 - 15

请参阅

Join the table to a subquery that uses ROW_NUMBER() window function to sort the rows and filter the rows that you want to be deleted:

DELETE c
FROM customers c
INNER JOIN (
  SELECT *, ROW_NUMBER() OVER (ORDER BY register_date) rn
  FROM customers
  WHERE register_date > '2012-01-01'
) t ON t.ID = c.ID
WHERE t.rn > 5 AND t.rn <= 15; -- get 10 rows with row numbers 6 - 15

See the demo.

回复收藏 0 原文

累赘 2025-02-14 02:08:35

您可以尝试使用 row_number 窗口功能，然后捕获那些等级位置在5到15之间的行。

DELETE FROM customers
WHERE ID IN (
    SELECT * 
    FROM (SELECT ID, 
                 ROW_NUMBER() OVER(
                     ORDER BY IF(register_date>'2012-01-01', 0, 1)
                              register_date                       ) AS rn
          FROM customers) ranked_ids
    WHERE rn > 4 
      AND rn < 16
);

尽管取得了相同的结果，但这将安全避免使用limit。

编辑。加入。

DELETE FROM customers c
INNER JOIN (SELECT ID, 
                   ROW_NUMBER() OVER(
                        ORDER BY IF(register_date>'2012-01-01', 0, 1)
                                    register_date                       ) AS rn
                  FROM customers) ranked_ids
            WHERE 
           ) ids_to_delete
        ON c.ID = ids_to_delete.ID
       AND ids_to_delete.rn > 4 
       AND ids_to_delete.rn < 16

You could try assigning a rank to your rows with the ROW_NUMBER window function, then catch those rows whose rank position is between 5 and 15.

DELETE FROM customers
WHERE ID IN (
    SELECT * 
    FROM (SELECT ID, 
                 ROW_NUMBER() OVER(
                     ORDER BY IF(register_date>'2012-01-01', 0, 1)
                              register_date                       ) AS rn
          FROM customers) ranked_ids
    WHERE rn > 4 
      AND rn < 16
);

This would safely avoid the use of LIMIT, though achieves the same result.

EDIT. Doing it with a join.

DELETE FROM customers c
INNER JOIN (SELECT ID, 
                   ROW_NUMBER() OVER(
                        ORDER BY IF(register_date>'2012-01-01', 0, 1)
                                    register_date                       ) AS rn
                  FROM customers) ranked_ids
            WHERE 
           ) ids_to_delete
        ON c.ID = ids_to_delete.ID
       AND ids_to_delete.rn > 4 
       AND ids_to_delete.rn < 16

回复收藏 0 原文

~没有更多了~