sql - 需要在 postgreSQL 中获取具有最小值的行

Question

我需要获取价格最低的完整行。此处的价格将根据特价及其日期在查询中计算。如果具有不同 place_id 的 2 行的价格相同，那么它应该获取任何行。

看看下面目前使用的结构。

考虑所有行的存货都是“1”；

价格表:

product_id  | place_id | price    | special_price | special_date_from   | special_date_to
--------------------------------------------------------------------------------------------
27          |27        |1000.0000 |0.0000         |                     |
26          |27        |500.0000  |129.0000       |2015-05-15 00:00:01  |2015-08-30 23:59:59
26          |24        |1500.0000 |0              |                     |
27          |5         |56224.0000|0              |                     |
27          |128       |1000.0000 |100.0000       |2015-07-31 00:00:01  |2015-08-12 23:59:59
27          |121       |100.0000  |0              |                     |
26          |121       |500.0000  |0              |                     |

我的查询是:

select * 
  from ( 
        (select min(price) price, 
                myt.product_id 
           from ( select (case when 
                              (cpp.special_price_fromdate <= '2015-08-04 19:18:49' 
                               and cpp.special_price_todate >= '2015-08-04 19:18:49' 
                               and cpp.special_price > 0) 
                               then cpp.special_price else cpp.price end
                          ) as price,  
                          cpp.product_id, 
                          cpp.place_id
                   from product_price as cpp 
                  where cpp.in_stock > 0   
                    and cpp.place_id IN (130,27,128,129,126,121,54)
                ) as myt group by product_id
        ) t1 
inner join
    (select DISTINCT on(pps.product_id) 
            (case when (pps.special_price_fromdate <= '2015-08-04 19:18:49' 
                        and pps.special_price_todate >= '2015-08-04 19:18:49' 
                        and pps.special_price > 0) 
                  then pps.special_price 
                  else pps.price end) as price, 
            pps.product_id,
            pps.price as old_price, 
            pps.place_id 
       from product_price pps 
      where pps.in_stock > 0
    ) t2 on t1.price = t2.price 
            and t1.product_id = t2.product_id 
            and t1.product_id in ('26','27')
) AS "pp";

我想要的结果是:

product_id  | place_id | price    | old_price     
--------------------------------------------------
26          | 27       | 129.0000 | 500.0000      
27          | 121      | 100.0000 | 100.0000

但是我根据上面的查询得到了结果:

product_id  | place_id | price    | old_price     
--------------------------------------------------
26          | 27       | 129.0000 | 500.0000

27 product_id 已被跳过，因为我在“On Condition”中检查过的价格相同。我不知道为什么:(

Answer 1

select product_id, price_id, price from
(
select *, row_number() over(partition by product_id order by price,place_id) as rn
from product_price
)
where rn = 1;

您可以使用row_number 函数对以1 开头的行进行编号，以获取每个分区中的最低价格。根据需要为其他列添加更多计算。