mysql - ORDER BY 基于最大行数出现

Question

表格字段:shop_id , product_id

1. 我想要一份包含特定产品的所有商店的列表(应该至少有 1 种产品)
2. 结果应根据指定产品数量最多的商店进行排序

我可以为第 1 部分编写 sql 查询，但列表未根据匹配最大产品数的商店排序

SELECT 
    shop_id,
    product_id
FROM
    products_table
WHERE
    product_id IN (1,2,3)
ORDER BY ???

是否有最优解？

Answer 1

加入一个子查询，获取每个商店的计数，并以此为准。

SELECT a.shop_id, a.product_id
FROM products_table AS a
JOIN (SELECT shop_id, COUNT(*) AS product_count
      FROM products_table
      WHERE product_id in (1, 2, 3)
      GROUP BY shop_id) AS b
ON a.shop_id = b.shop_id
WHERE product_id IN (1, 2, 3)
ORDER BY b.product_count DESC