按测试id选择最低价格组

bn31dyow 于 2021-06-15 发布在 Mysql

关注(0)|答案(3)|浏览(309)

我有一个查询，它按以下结构顺序返回结果，按价格升序排列

SELECT our_price,
       mrp,
       marketplace_id,
       test_id
FROM   marketplace_test_mapping mtm
       INNER JOIN marketplace m
               ON m.id = mtm.marketplace_id
WHERE  m.city_id = 1
       AND mtm.test_id IN ( 36, 23, 43, 107,
                            121, 82 )
ORDER  BY our_price ASC;

输出

our_price | mrp | marketplace_id | test_id
----------+-----+----------------+--------
50         90     3                23
51         70     2                23
52         88     1                23
53         80     3                24
54         90     2                24
55         90     4                23
56         90     1                25
57         90     2                25
58         90     1                24

我想为每个测试id取最低价格，但当我分组时 test_id ，它对 marketplace_id 以及返回以下内容

SELECT *
FROM   (SELECT our_price,
               mrp,
               marketplace_id,
               test_id
        FROM   marketplace_test_mapping mtm
               INNER JOIN marketplace m
                       ON m.id = mtm.marketplace_id
        WHERE  m.city_id = 1
               AND mtm.test_id IN ( 36, 23, 43, 107,
                                    121, 82 )
        ORDER  BY our_price ASC) AS temp_marketplace_test_mapping
GROUP  BY test_id;

实际产量

our_price | mrp | marketplace_id | test_id
----------+-----+----------------+--------
52         88     1                23
58         90     1                24
56         90     1                25

预期产量

our_price | mrp | marketplace_id | test_id
----------+-----+----------------+--------
50         90     3                23
53         90     3                24
56         90     1                25

两种结果的差别是 actual output 正在排序 marketplace_id 当按“开”分组时也是如此 test_id .

sql mysql Database

来源：https://stackoverflow.com/questions/53828815/select-minimum-our-price-group-by-test-id

3条答案

按热度按时间

gajydyqb1#

“我想获取每个测试id的最低价格”

select test_id, min(our_price) as our_price_min -- changed
from marketplace_test_mapping  mtm
inner join marketplace m on m.id=mtm.marketplace_id
where m.city_id=1 and mtm.test_id in (36,23,43,107,121,82)
group by test_id -- added
order by our_price asc;

赞(0）回复(0）举报 2021-06-16

snvhrwxg2#

找到每个测试id的最低价格，然后得到市场id和mrp。

with transactions as (
  -- get the transactions list, execute filters
  SELECT test_id, marketplace_id, mrp, our_price       
  FROM   marketplace_test_mapping mtm
  INNER JOIN marketplace m ON m.id = mtm.marketplace_id
  WHERE  m.city_id = 1
  AND mtm.test_id IN (23,36,43,82,107,121) --it's a good practice to sorta data in IN clause
)

-- if there are few marketplaces with minimum price on each test_id
-- then each of then will be shown
select a.*, b.marketplace_id, b.mrp
from (
  -- minimum price for each test_id
  select test_id, min(our_price) as our_price_min
  from transactions
  group by test_id
) a
left join transactions b on a.test_id = b.test_id and a.our_price_min = b.our_price
order by a.test_id, b.marketplace_id

赞(0）回复(0）举报 2021-06-16

nmpmafwu3#

WITH cte (our_price,mrp,marketplace_id,test_id) AS
(SELECT our_price,mrp,marketplace_id,test_id
FROM   marketplace_test_mapping mtm
       INNER JOIN marketplace m
       ON m.id = mtm.marketplace_id
WHERE  m.city_id = 1 AND mtm.test_id IN ( 36, 23, 43, 107,121, 82 )
)
select t1.our_price, t1.mrp, t1.marketplace_id, t1.test_id from cte t1 
join (select test_id, min(our_price) our_price from cte group by test_id) t2
on t1.test_id = t2.test_id and t1.our_price = t2.our_price;

赞(0）回复(0）举报 2021-06-15

我来回答

按测试id选择最低价格组

3条答案

相关问题

热门标签

最新问答