卡在构建MySQL查询中 [英] Stuck in building MySQL query

问题描述

举一个表格的例子:

id | item_id | user_id | bid_price
----------------------------------

任务是为提供的集合中的每个item_id选择行，并选择最小 bid_price.

The task is to select rows with minimum bid_price for each item_id in the provided set.

例如:item_id = [1, 2, 3]-因此，我需要选择最多三(3)行，最小行为bid_price.

For example: item_id = [1, 2, 3] - so I need to select up to three (3) rows, having a minimum bid_price.

数据示例:

id | item_id | user_id | bid_price
----------------------------------
 1 |    1    |   11    |     1
 2 |    1    |   12    |     2
 3 |    1    |   13    |     3
 4 |    1    |   14    |     1
 5 |    1    |   15    |     4
 6 |    2    |   16    |     2
 7 |    2    |   17    |     1
 8 |    3    |   18    |     2
 9 |    3    |   19    |     3
10 |    3    |   18    |     2

预期结果:

id | item_id | user_id | bid_price
----------------------------------
 1 |    1    |   11    |     1
 7 |    2    |   17    |     1
 8 |    3    |   18    |     2

实际上，我使用的是Symfony/Docine DQL，但是用一个简单的SQL示例就足够了.

Actually, I'm using Symfony/Docine DQL, but it will be enough with a plain SQL example.

推荐答案

对于行中的所有列，您都可以在subselect上使用内部联接以获取最低出价

For the all the columns in the rows you could use a inner join on subselect for min bid price

select m.id, m.item_id, m.user_id, m.bid_price
from my_table m 
inner join ( 
select item_id, min(id) min_id,  min(bid_price) min_price
from my_table 
where   item_id IN (1,2,3)
group by item_id 
) t on t.item_id = m.item_id 
   and t.min_price= m.bid_price
   and t.min_id = m.id

或..如果您有一些float数据类型，则可以使用acst来表示未签名

or .. if you have some float data type you could use a acst for unsigned

  select m.id, m.item_id, m.user_id, cast(m.bid_price as UNSIGNED) 
  from my_table m 
  inner join ( 
  select item_id, min(id) min_id,  min(bid_price) min_price
  from my_table 
  where   item_id IN (1,2,3)
  group by item_id 
  ) t on t.item_id = m.item_id 
     and t.min_price= m.bid_price
     and t.min_id = m.id 

这篇关于卡在构建MySQL查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！