具有多个表联接的每组前N个

Question

根据我的研究，这是一个非常普遍的问题，通常有一个相当简单的解决方案。我的任务是更改几个查询，以 使所有结果都 进入 每组前3名
。最初，一切进展顺利，我使用了该站点的一些建议和答案来实现这一目标（最受欢迎的产品）。但是，由于多次加入，我在最后一个“最畅销产品”方面遇到了困难。

基本上，我需要 按＃个产品的最高销售顺序来排序所有产品，其中每个供应商的最大产品数量为3。
我要联接多个表来创建原始查询，并且每次尝试使用变量生成时排名会产生无效的结果。以下内容应有助于更好地理解该问题（为简便起见，我已删除了不必要的字段）：

产品表

productid | vendorid | approved | active | deleted

供应商表

vendorid | approved | active | deleted

订单表

orderid | `status` | deleted

订单项目表

orderitemid | orderid | productid | price

现在， 获取所有结果的 原始查询如下：

SELECT COUNT(oi.price) AS `NumSales`, 
       p.productid, 
       p.vendorid
FROM products p
INNER JOIN vendors v ON (p.vendorid = v.vendorid)
INNER JOIN orders_items oi ON (p.productid = oi.productid)
INNER JOIN orders o ON (oi.orderid = o.orderid)
WHERE (p.Approved = 1 AND p.Active = 1 AND p.Deleted = 0)
AND (v.Approved = 1 AND v.Active = 1 AND v.Deleted = 0)
AND o.`Status` = 'SETTLED'
AND o.Deleted = 0
GROUP BY oi.productid
ORDER BY COUNT(oi.price) DESC
LIMIT 100;

最终，（这是我很困惑的地方），我试图更改以上声明，以使每个供应商仅收到排名前三的产品（按＃出售）。我要补充到目前为止的内容，但这样做很尴尬，这个问题已经是一堵墙了。我试过变量，但一直得到无效的结果。任何帮助将不胜感激。

Answer 1

即使您指定LIMIT 100，这种类型的查询也将需要建立完整的扫描和表，然后在最终过滤要显示的100之前检查每个记录并对行进行编号。

select
    vendorid, productid, NumSales
from
(
    select
        vendorid, productid, NumSales,
        @r := IF(@g=vendorid,@r+1,1) RowNum,
        @g := vendorid
    from (select @g:=null) initvars
    CROSS JOIN 
    (
        SELECT COUNT(oi.price) AS NumSales, 
               p.productid, 
               p.vendorid
        FROM products p
        INNER JOIN vendors v ON (p.vendorid = v.vendorid)
        INNER JOIN orders_items oi ON (p.productid = oi.productid)
        INNER JOIN orders o ON (oi.orderid = o.orderid)
        WHERE (p.Approved = 1 AND p.Active = 1 AND p.Deleted = 0)
        AND (v.Approved = 1 AND v.Active = 1 AND v.Deleted = 0)
        AND o.`Status` = 'SETTLED'
        AND o.Deleted = 0
        GROUP BY p.vendorid, p.productid
        ORDER BY p.vendorid, NumSales DESC
    ) T
) U
WHERE RowNum <= 3
ORDER BY NumSales DESC
LIMIT 100;

这里的方法是

1. 分组以获取NumSales
2. 使用变量对每个供应商/产品的销售额进行行编号
3. 过滤编号的数据集，以允许每个供应商最多3个
4. 通过NumSales DESC订购其余产品，仅退还100