昵称: zjyss
原文地址:https://www.cnblogs.com/zjyss/p/15701439.html
1.可以循环表取出相同字段的第一条去建立临时表或视图
2.使用pg的row_number 函数对相同字段记录分组排序,取出排序分组记录中的第一个。
下例即取出查询结果集合中产品对应date最新的那一条数据集合,相当于根据product_id去重,保留date最大的一条
select s.product_id, s.price from ( select *, row_number() over (partition by ttt.product_id order by ttt.date desc) as group_idx from ( select distinct (kpol.product_id),kpol.price, sm.date from kthrp_purchase_order_line kpol left join stock_move sm on split_part(sm.ref, ',', 1) = 'kthrp.purchase.order.line' and split_part(sm.ref, ',', 2)::INTEGER = kpol.id left join product_product pp on pp.id = kpol.product_id where sm.date is not null order by sm.date desc ) ttt ) s where s.group_idx = 1;
可以简化为:
select s.field_name_s, s.field_name_x from ( select *, row_number() over (partition by ttt.field_name_a order by ttt.field_name_b desc) as group_idx from 子查询 ttt ) s where s.group_idx = 1;
1.row_number() 为返回的记录定义各行编号
2.pritition by 分组
3.order by 排序
标签:product,postgresql,只取,ttt,最新,sm,date,order,id From: https://www.cnblogs.com/chenghu/p/16613089.html