苦苦挣扎,这似乎是一个简单的选择。我有一个报价表,其中包含价格,供应商和SKU。多个供应商可以销售同一个 SKU,同一个供应商也可以多次销售该 SKU。我需要每个供应商针对特定 SKU 提供的三个最低价格的报价。
offers
+-----+--------+-------+
| sku | vendor | price |
+-----+--------+-------+
| abc | vend_a | 1.00 |
| abc | vend_a | 2.00 |
| abc | vend_a | 3.00 |
| abc | vend_a | 4.00 |
| abc | vend_a | 5.00 |
| abc | vend_b | 1.00 |
| abc | vend_b | 2.00 |
| abc | vend_b | 3.00 |
| abc | vend_b | 4.00 |
| abc | vend_b | 5.00 |
| abc | vend_c | 1.00 |
| abc | vend_c | 2.00 |
| abc | vend_c | 3.00 |
| abc | vend_c | 4.00 |
| abc | vend_c | 5.00 |
+-----+--------+-------+
所以选择后,我想要这个
result
+-----+--------+-------+
| sku | vendor | price |
+-----+--------+-------+
| abc | vend_a | 1.00 |
| abc | vend_a | 2.00 |
| abc | vend_a | 3.00 |
| abc | vend_b | 1.00 |
| abc | vend_b | 2.00 |
| abc | vend_b | 3.00 |
| abc | vend_c | 1.00 |
| abc | vend_c | 2.00 |
| abc | vend_c | 3.00 |
+-----+--------+-------+
做了一些搜索,但没有找到任何给我这种类型的结果。我感谢您的帮助。
您可以使用变量来模拟ROW_NUMBER
窗口函数,这在MySQL中不可用:
SELECT vendor, price
FROM (
SELECT vendor, price,
@rn := IF(@v = vendor, @rn + 1,
IF(@v := vendor, 1, 1)) AS rn
FROM offers
CROSS JOIN (SELECT @rn := 0, @v := '') AS vars
WHERE sku = 'abc'
ORDER BY vendor, price) AS t
WHERE t.rn <= 3
变量@rn
枚举每个vendor
分区中的记录。 @rn=1
对应于分区中价格最低的记录,@rn=2
对应于下一条记录,依此类推。
在这里演示