oracle over(partition BY) 函数用法

kitajima--

于 2017-01-16 11:14:36 发布

阅读量649

点赞数 1

分类专栏： Oracle 10g

本文链接：https://blog.csdn.net/masuwen/article/details/54572338

版权

Oracle 10g 专栏收录该内容

33 篇文章

订阅专栏

本文介绍了一种使用SQL进行数据去重并按接收时间降序排列的方法。通过子查询结合row_number()窗口函数实现去除重复记录，并保留每个用户最新的邮件接收时间。此技巧适用于需要处理大量邮件数据的应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

sql:

SELECT u.user_id,
    u.user_name,
    u.mail_receive_time
FROM mail_user u,
    mail_interior i
WHERE u.mail_id          = i.mail_id
AND i.mail_creator       = '8440'
AND u.mail_status       <> 3
AND u.user_id           <> i.mail_creator
AND u.mail_receive_time IS NOT NULL
ORDER BY u.mail_receive_time DESc;
前：

--去重复后

SELECT u.user_id,u.user_name,u.mail_receive_time
FROM
(SELECT a.* ,
    row_number() over(partition BY a.user_id order by a.mail_receive_time DESC)rn
FROM
    (SELECT u.user_id,
      u.user_name,
      u.mail_receive_time
    FROM mail_user u,
      mail_interior i
    WHERE u.mail_id          = i.mail_id
    AND i.mail_creator       = '8440'
    AND u.mail_status       <> 3
    AND u.user_id           <> i.mail_creator
    AND u.mail_receive_time IS NOT NULL
    ) a
)u
WHERE rn=1
order by u.mail_receive_time desc;