问题描述
给出一个表格的例子:
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.idGiven 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
这篇关于坚持构建 MySQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！