postgresql - 在查询中获取最高和最低价格和卖家

Question

cod_Prod   product seller price
  123        A     XPTO    10
  123        A     POTY    20
  123        A     MUY     15

我查询产品的最高和最低价格如下：

select cod_Prod, product, max(price) as maxprice, min(price) as minprice
from tablename
group by cod_Prod, product

我明白了，这是正确的：

cod_Prod product maxprice  minprice
  123      A       20       10 

如何在同一查询中获得最高和最低价格的卖家，如下输出：

cod_Prod product maxprice seller_maxprice minprice seller_minprice
  123      A        20        POTY          10           XPTO

标签： postgresql

Answer 1

与旋转逻辑一起使用ROW_NUMBER将是一种方法：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY cod_Prod ORDER BY price) rn_low,
              ROW_NUMBER() OVER (PARTITION BY cod_Prod ORDER BY price DESC) rn_high
    FROM tablename
)

SELECT
    cod_Prod,
    product,
    MAX(price)  FILTER (WHERE rn_high = 1) AS maxprice,
    MAX(seller) FILTER (WHERE rn_high = 1) AS seller_maxprice,
    MAX(price)  FILTER (WHERE rn_low = 1)  AS minprice,
    MAX(seller) FILTER (WHERE rn_low = 1)  AS seller_minprice
FROM cte
GROUP BY
    cod_Prod,
    product;

演示