坚持构建 MySQL 查询

给出一个表格的例子:

id |item_id |用户 ID |竞标价格----------------------------------

任务是为提供的集合中的每个 item_id 选择具有 minimum bid_price 的 rows.p>

例如:item_id = [1, 2, 3] - 所以我需要选择最多三 (3) 行，且具有最低 bid_price.p>

数据示例:

id |item_id |用户 ID |竞标价格----------------------------------1 |1 |11 |12 |1 |12 |23 |1 |13 |34 |1 |14 |15 |1 |15 |46 |2 |16 |27 |2 |17 |18 |3 |18 |29 |3 |19 |310 |3 |18 |2

预期结果:

id |item_id |用户 ID |竞标价格----------------------------------1 |1 |11 |17 |2 |17 |18 |3 |18 |2

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

解决方案

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

选择 m.id, m.item_id, m.user_id, m.bid_price从 my_table m内部联接 (选择 item_id, min(id) min_id, min(bid_price) min_price来自 my_table其中 item_id IN (1,2,3)按 item_id 分组) t 上 t.item_id = m.item_id和 t.min_price=m.bid_price和 t.min_id = m.id

或者 .. 如果你有一些浮点数据类型，你可以使用 acst 来表示无符号

 select m.id, m.item_id, m.user_id, cast(m.bid_price as UNSIGNED)从 my_table m内部联接 (选择 item_id, min(id) min_id, min(bid_price) min_price来自 my_table其中 item_id IN (1,2,3)按 item_id 分组) t 上 t.item_id = m.item_id和 t.min_price=m.bid_price和 t.min_id = m.id

Given an example of table:

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

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

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

Example of data:

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

Expected result:

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

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

解决方案

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

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