代码之家 › 专栏 › 技术社区 › mamesaye

在相似记录中选择最新更新记录

postgresql-9.1 select join datetime postgresql

-2

mamesaye · 技术社区 · 7 年前

我有两个表段和摘要。
我想列出一个日期间隔的节目,只显示最新更新的部分(粗体),因为它是最新的部分为该人的副本。
以下是我以前的查询结果:

select seg.summ, su.channel, seg.block_id, seg.person, seg.updated_at as segupdate 
from segments seg 
   left join summaries su on seg.summ = su.id 
where su.created_at between '2018-06-06' and '2018-07-13' 
order by summ, block_id, seg.updated_at desc;

我想要这个:

2 回复 | 直到 7 年前

D-Shih 7 年前

你可以试着用 ROW_NUMBER 窗口函数,生成行号 order by segupdate DESC rn = 1 最新数据行。

SELECT * FROM 
(
     SELECT *,ROW_NUMBER() OVER(PARTITION BY summ,channel,block_id,person order by segupdate DESC) rn
     FROM (
        select seg.summ, su.channel, seg.block_id, seg.person, seg.updated_at as segupdate 
         from segments seg 
         left join summaries su on seg.summ = su.id 
         where su.created_at between '2018-06-06' and '2018-07-13' 
     ) t1
) t1
where rn = 1

a_horse_with_no_name 7 年前

我就是这么做的:

select distinct on (seg.summ, seg.block_id, seg.person) su.channel, seg.updated_at as segupdate 
from segments seg 
  left join summaries su on seg.summ = su.id 
where su.created_at between '2018-06-06' and '2018-07-13' 
order by summ, block_id, person, seg.updated_at desc;

推荐文章