首页 >

每个顾客最近一次下单的订单信息

抓住那只蝉头像 抓住那只蝉 
select 
    t1.order_id,
    t1.customer_name,
    t1.order_date
from (
    select
        t1.order_id,
        t1.order_date,
        t2.customer_name,
        row_number() over(partition by t1.customer_id order by t1.order_date desc) as rn
    from orders t1
    left join customers t2
    on t1.customer_id = t2.customer_id
) t1
where rn = 1
# order by order_id

查看原题
添加回答
想开了的秋田犬头像 想开了的秋田犬
这不就是SQL82 吗？
发表于 2025-04-24 15:47:38 回复(1)
野蛮的熊熊求你们别卷了头像 野蛮的熊熊求你们别卷了 
select order_id,customer_name,order_date from
(select *,row_number() over(partition by customer_id order by order_date desc) rnk
from orders ) new
left join customers using(customer_id)
where rnk=1
order by customer_name

发表于 2026-03-27 09:43:47 回复(0)
马国成好样的头像 马国成好样的
重复了吧
发表于 2025-07-08 14:49:03 回复(0)
4fbd81e头像 4fbd81e
题目描述要求使用窗口函数和表联结进行解决。本份代码确实使用了这些知识点。

with t1 as(
    select o.*,  c.customer_name,
    row_number() over(partition by customer_id order by order_date desc) as rn
    from orders o
    join customers c
    on o.customer_id=c.customer_id
)
select order_id, customer_name, order_date
from t1
where rn=1
order by customer_name asc


# 要求使用窗口函数和表联结进行解决。
# 本份代码确实使用了这些知识点


发表于 2025-04-18 16:02:39 回复(0)
抓住那只蝉头像 抓住那只蝉 
select 
    t1.order_id,
    t1.customer_name,
    t1.order_date
from (
    select
        t1.order_id,
        t1.order_date,
        t2.customer_name,
        row_number() over(partition by t1.customer_id order by t1.order_date desc) as rn
    from orders t1
    left join customers t2
    on t1.customer_id = t2.customer_id
) t1
where rn = 1
# order by order_id

发表于 2026-04-14 22:49:17 回复(0)
只会写SELECT头像 只会写SELECT 
select q1.order_id,q2.customer_name,q1.order_date
from (
    select *,row_number()over(partition by customer_id order by order_date desc) order_rank
    from orders
) q1 
left join customers q2 on q1.customer_id=q2.customer_id
where q1.order_rank=1
order by customer_name

发表于 2026-02-08 15:16:28 回复(0)
虫哇头像 虫哇
select t1.order_id, c.customer_name, t1.order_date
from(
    select *,
    row_number() over(partition by customer_id order by order_date  desc) as rn
    from orders
) t1
join customers c
on t1.customer_id = c.customer_id
where rn = 1
group by order_id, customer_name

发表于 2026-02-02 16:42:04 回复(0)
可爱的王者天天摸鱼头像 可爱的王者天天摸鱼
1
子查询
select order_id,customer_name,order_date from(
select order_id,customer_id,order_date
from orders where(customer_id,order_date) in(
select customer_id,max(order_date) as order_date from
orders group by customer_id))t
join customers on t.customer_id=customers.customer_id
order by customer_name
2 窗口函数
select order_id,customer_name,order_date from (
select order_id,customer_id,order_date,row_number() over(partition by customer_id order by order_date desc) as rk from
orders )t join customers c
on t.customer_id=c.customer_id
where rk =1
发表于 2026-01-14 11:33:26 回复(0)
爱吃肉的勇士说这不是bug头像 爱吃肉的勇士说这不是bug
select
    order_id,
    customer_name,
    order_date
from
    (
        select
            *,
            row_number() over (
                partition by
                    customer_id
                order by
                    order_date desc
            ) as rk
        from
            orders
    ) t
    left join customers c on t.customer_id = c.customer_id
where
    rk = 1
order by
    customer_name

发表于 2025-12-31 17:37:31 回复(1)
风流倜傥三金老师头像 风流倜傥三金老师
/*
这题使用GROUP BY + MAX同样可以达成，但是效率与可读性不如窗口函数，这是窗口函数的基础应用
*/

WITH
t1 AS (     -- 使用窗口函数对每位客户的订单以倒序排名
SELECT
    o.order_id,
    c.customer_id,
    c.customer_name,
    o.order_date,
    ROW_NUMBER() OVER(PARTITION BY c.customer_id ORDER BY o.order_date DESC) AS rk
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id
)

SELECT      -- 输出每位客户最新订单的信息
    order_id,
    customer_name,
    order_date
FROM t1
WHERE rk = 1
ORDER BY customer_name;
发表于 2025-12-28 22:19:14 回复(1)
有胆量的托尼在迎接offer头像 有胆量的托尼在迎接offer
select
    order_id,
    customer_name,
    order_date
from (
    select
        o.order_id,
        o.order_date,
        c.customer_name,
        dense_rank() over(partition by o.customer_id order by order_date desc) as rk
    from orders o
        join customers c on o.customer_id = c.customer_id
)tmp
where rk = 1
发表于 2025-12-16 15:39:56 回复(0)
张彧0229头像 张彧0229
select
    order_id,
    customer_name,
    order_date
from
    (
        select
            order_id,
            customer_name,
            order_date,
            rank() over (
                partition by
                    o.customer_id
                order by
                    order_date desc
            ) rk
        from
            orders o
            left join customers c on o.customer_id = c.customer_id
    ) t
where
    t.rk = 1
order by
    t.customer_name

求出最大的order_date ，不应该用max()over(partition by )吗?
发表于 2025-11-26 15:47:48 回复(0)
除却巫山是小婷头像 除却巫山是小婷
select order_id,customer_name,order_date
from 
(select order_id,customer_name,order_date,rank() over(PARTITION by o.customer_id order by order_date desc ) a
from orders o  
left join customers c  on o.customer_id=c.customer_id) t
where a=1
发表于 2025-11-03 09:43:39 回复(0)
你是最棒的zy头像 你是最棒的zy 
select order_id,customer_name,order_date
from orders
left join customers on customers.customer_id=orders.customer_id
where order_date in(
    select max(order_date)
    from orders 
    group by customer_id
)
order by orders.customer_id

发表于 2025-10-17 11:34:07 回复(0)
像初雪一样遇见你头像 像初雪一样遇见你 
select order_id,customer_name,order_date
from
(select
order_id
,customer_name
,order_date
,rank() over(partition by customer_id order by order_date desc) as rk
from orders
join customers
using(customer_id)) e
where rk=1

发表于 2025-10-16 20:25:09 回复(0)
破烂儿_头像 破烂儿_ 
with base as (
    select 
    o.order_id,
    customer_name,
    order_date,
    dense_rank() over(partition by customer_name order by order_date desc) as rk
from orders o
join customers c on c.customer_id = o.customer_id
)
select 
    order_id,
    customer_name,
    order_date
from base 
where rk = 1

发表于 2025-10-15 10:17:04 回复(0)
程序员Realeo头像 程序员Realeo 
select
    order_id,
    customer_name,
    order_date
from
    (
        select
            order_id,
            customer_name,
            order_date,
            row_number() over (
                partition by
                    a.customer_id
                order by
                    order_date desc
            ) as cnt
        from
            orders a,
            customers b
        where
            a.customer_id = b.customer_id
    ) c
where
    cnt = 1
order by
    customer_name

发表于 2025-09-11 15:15:10 回复(0)
矫健的小刺猬在看牛客头像 矫健的小刺猬在看牛客 
select
    order_id
    ,customer_name
    ,order_date
from
    (
        select
            order_id
            ,customer_name
            ,order_date
            ,row_number() over(partition by o.customer_id order by order_date desc) as order_rk
        from orders o left join customers c on o.customer_id = c.customer_id
    ) as temp
where order_rk = 1
;

发表于 2025-09-01 17:19:28 回复(0)
牛客931948580号头像 牛客931948580号 
with a as (select customer_name , max(order_date) as order_date  
from orders o 
join customers c 
using(customer_id)
group by customer_name) 

select order_id, customer_name, order_date 
from orders o 
join a 
using (order_date )
order by customer_name

发表于 2025-08-26 08:57:46 回复(0)
Jeagle头像 Jeagle 
WITH t1 AS(
    SELECT order_id, customer_name, order_date,
    RANK() OVER(PARTITION BY a.customer_id ORDER BY order_date DESC ) AS rk
    FROM orders AS a
    LEFT JOIN customers AS b
    ON  a.customer_id = b.customer_id
)

SELECT order_id, customer_name, order_date
FROM t1
WHERE rk = 1;

发表于 2025-08-13 22:48:56 回复(0)
提交观点

问题信息

SQL
难度：
32条回答 518浏览

热门推荐

相关试题

每个顾客最近一次下单的订单信息

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号