oracle over(partition BY) 函数用法

最新推荐文章于 2020-07-14 11:30:15 发布

原创最新推荐文章于 2020-07-14 11:30:15 发布 · 755 阅读

0 ·

CC 4.0 BY-SA版权

Oracle 10g 专栏收录该内容

33 篇文章

订阅专栏

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

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;