如何从表中选择除最后 100 条之外的所有记录 [英] How to select all records from table apart from the last 100

问题描述

我有一个存储客户记录的数据库，我想设置一个 cron 作业来定期覆盖这些记录.我想说 Select * from ORDERS 其中 ORDER_ID 不在列表的前 100 名中.每行都有自己的 order_id，最新的 order_id 是最新的订单.如果出现问题，我需要保留最新的 100 个订单 ID.感谢您的时间.

I have a database that stores customers records and I would like to set up a cron job to overwrite these records periodically. I would like to say Select * from ORDERS where ORDER_ID is not in the top 100 of the list. Each row has its own order_id with the latest order_id being the latest order. I need to keep the latest 100 order ids in case of some problems. Thank you for your time.

推荐答案

您可以左连接包含 100 个最后一个 order_id 的行集 - 这将导致左连接集中除最后 100 个之外的所有行集都为 NULL.

You can left join a rowset of 100 last order_id's - this will result in all but 100 last having NULL in the left joined set.

SELECT o.* from `order-table` o
LEFT JOIN
  ( SELECT order_id FROM `order-table` ORDER BY order_id DESC LIMIT 100 ) o100
ON o.order_id = o100.order_id
WHERE o100.order_id IS NULL

这篇关于如何从表中选择除最后 100 条之外的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！