mysql - MySQL查询列表记录

我有下面的mysql表：

STORES
store_id, rating
 10023      5
 10024      5
 10025      4
 10026      4

PRODUCTS
product_id, store_id, price
  10023         10023      5.00
  10045         10023     130.00
  10056         10023      10.00
  10057         10024      20.00
  10058         10024      12.00
  10059         10025       7.00


I need MYSQL query to retrieve records as below:
  store_id,  product_id  Price
    10023       10056    5.00
    10023       10023    10.00
    10024       10058    12.00
    10024       10057    20.00
    10025       10059     7.00

什么是正确的SQL查询，可以根据最高的商店评级列出记录，然后是每个商店的最便宜价格和限制2？

最佳答案

MySQL没有基于一组行（例如oracle）计算聚合值的分析函数，但是可以使用变量限制单个查询中每个字段值的行数。我试过了，看起来很管用：

SELECT
r.store_id,
r.product_id,
r.price
FROM (
      SELECT *
      FROM (
            SELECT *,
            @cnt := if(@store_id = s.store_id, @cnt:= @cnt + 1, 1) AS row_count,
            @store_id:=s.store_id AS a
            FROM stores AS s
            LEFT JOIN (SELECT store_id AS sid, product_id, price
                       FROM products ORDER BY store_id,price ASC) AS p
            ON s.store_id=p.sid WHERE product_id IS NOT NULL
           ) AS b, (SELECT @store_id:='',@cnt:=0) AS c
      HAVING row_count<=2
      ORDER BY rating DESC,store_id ASC,price ASC
     ) AS r;