oracle over(partition BY) 函数用法

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,
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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!