更新时间:2023-01-28 19:31:06
假设create_time包含创建订单的时间,并且您希望100个具有最新订单的客户,您可以:
Assuming that create_time contains the time the order was created, and you want the 100 clients with the latest orders, you can:
create_time desc
create_time desc
ROWNUM
查询:
SELECT * FROM (
SELECT * FROM (
SELECT
id,
client_id,
create_time,
ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn
FROM order
)
WHERE rn=1
ORDER BY create_time desc
) WHERE rownum <= 100
Oracle 12c的更新
从版本12.1开始,Oracle引入了真正的" Top-N查询.使用新的FETCH FIRST...
语法,您还可以使用:
With release 12.1, Oracle introduced "real" Top-N queries. Using the new FETCH FIRST...
syntax, you can also use:
SELECT * FROM (
SELECT
id,
client_id,
create_time,
ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn
FROM order
)
WHERE rn = 1
ORDER BY create_time desc
FETCH FIRST 100 ROWS ONLY)