我有一个类似于这样的表:
id fkey srno remark date
1 A001 1
2 A001 2
3 A002 1
4 A003 1
5 A002 2
我希望你能够根据最大SRNO基于不同的最新记录,就像这样。
2 A001 2
4 A003 1
5 A002 2
我有一个类似于这样的表:
id fkey srno remark date
1 A001 1
2 A001 2
3 A002 1
4 A003 1
5 A002 2
2 A001 2
4 A003 1
5 A002 2
在Postgres中实现这一点的最佳方法是使用DISTINCT ON
:
SELECT DISTINCT ON (fkey) id, fkey, srno
FROM yourTable
ORDER BY fkey, srno DESC;
使用窗口函数 row_number()
select * from (
select *,row_number() over(PARTITION by fkey order by srno desc) rn from table1 t1
) t where rn=1
您可以使用CTE编写它
with cte as
(
select *,row_number() over(PARTITION by fkey order by srno desc) rn from
table_name t1
) select * from cte where rn=1
你可以使用相关子查询
select * from tablename where srno in
(select max(srno) from tablename b where a.fkey=b.fkey)
您可以使用带有IN
运算符的子查询
with tab(id, fkey, srno) as
(
select 1,'A001',1 union all
select 2,'A001',2 union all
select 3,'A002',1 union all
select 4,'A003',1 union all
select 5,'A002',2
)
select *
from tab
where ( fkey, srno ) in
(
select fkey, max(srno)
from tab
group by fkey
);
id fkey srno
2 A001 2
4 A003 1
5 A002 2