如何从表中选择除最后 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
相关文章