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

问题描述

我有一个存储客户记录的数据库，我想建立一个cron作业来定期覆盖这些记录.我想说从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个last_id的行集-这将导致除100个last_id之外的所有行都在左联接集中具有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屋！