psql rank row
rank() OVER (PARTITION BY f1 ORDER BY f2 DESC)
ROW_NUMBER() () OVER (PARTITION BY f1 ORDER BY f2 DESC)
为每个匹配的f1字段生成一个行号,并按照f2排序
例子
select DID,customerID,totalPrice,ROW_NUMBER() over(order by totalPrice) as rows from OP_Order
可以用户讲多列的数据排序后横向比较,例如,oe的一个询价单,向多个供应商询价后,需要取前3个供应商价格横列显示。
select product_id, product_qty, max(part1) as parter1, max(price1) as price1, max(part2) as parter2, max(price2) as price2, max(part3) as parter3, max(price3) as price3 from( select product_id, product_qty, case when abc=1 then partner_id end as part1, case when abc=2 then partner_id end as part2, case when abc=3 then partner_id end as part3, case when abc=1 then price_unit end as price1, case when abc=2 then price_unit end as price2, case when abc=3 then price_unit end as price3 from ( select pol.id, pol.product_id, pol.partner_id, product_qty , pol.price_unit, rank() OVER (PARTITION BY pol.product_id order by pol.partner_id) as abc from purchase_requisition as rq left join purchase_order as po on (po.requisition_id = rq.id) left join purchase_order_line as pol on (pol.order_id = po.id) where rq.id = 44 ) as t ) as tt group by product_id,product_qty
结果如下:
~~~~~~~
需要odoo 实施,二开,培训 等服务
QQ:190170444