基本上,我需要按每个产品的最高销售额排列所有产品,其中每个供应商的最高产品数量为3 有多个表被加入来创建原始查询,并且每次我尝试使用这些变量来生成排名时,都会产生无效结果。以下应该有助于更好地理解问题(为了简洁起见,我已经删除了不必要的字段):
产品表
productid | vendorid |批准|活动|删除供应商表格
vendorid |批准|活动|删除订单表
orderid | `状态`|已删除订单商品表
orderitemid | orderid | productid |价格现在,我对获取所有结果的原始查询如下所示:
SELECT COUNT(oi.price)AS`NumSales`, p.productid, p。 vendorid FROM产品p INNER JOIN供应商v ON(p.vendorid = v.vendorid) INNER JOIN orders_items oi ON(p.productid = oi.productid) INNER JOIN命令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;最后,(这里是我被困住的地方),我试图改变上面的语句这样,我只收到每个供应商的前3名产品(通过销售#)。我会补充到目前为止我所拥有的,但是我很尴尬地这样做,这个问题已经是一段文字了。我试过变量,但一直得到无效的结果。任何帮助将不胜感激。
解决方案即使您指定了LIMIT 100,这种类型的查询也需要完整扫描和表然后每个记录检查和排列编号,最后筛选出你想要显示的100个。
select vendorid,productid,NumSales from (选择 vendorid,productid,NumSales, @r:= IF(@ g = vendorid,@ r +1,1)RowNum, @g:= vendorid $ b $ from(select @g:= null)initvars CROSS JOIN ( SELECT COUNT(oi。价格)AS NumSales, p.productid, p.vendorid FROM产品p INNER JOIN供应商v ON(p.vendorid = v.vendorid) INNER JOIN orders_items oi ON(p.productid = oi.productid) INNER JOIN命令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;这里的方法是 Based on my research, this is a very common problem which generally has a fairly simple solution. My task is to alter several queries from get all results into get top 3 per group. At first this was going well and I used several recommendations and answers from this site to achieve this (Most Viewed Products). However, I'm running into difficulty with my last one "Best Selling Products" because of multiple joins. Basically, I need to get all products in order by # highest sales per product in which the maximum products per vendor is 3 I've got multiple tables being joined to create the original query, and each time I attempt to use the variables to generate rankings it produces invalid results. The following should help better understand the issue (I've removed unnecessary fields for brevity): Product Table Vendor Table Order Table Order Items Table Now, my original query to get all results is as follows: Finally, (and here's where I'm stumped), I'm trying to alter the above statement such that I received only the top 3 product (by # sold) per vendor. I'd add what I have so far, but I'm embarrassed to do so and this question is already a wall of text. I've tried variables but keep getting invalid results. Any help would be greatly appreciated. Even though you specify LIMIT 100, this type of query will require a full scan and table to be built up, then every record inspected and row numbered before finally filtering for the 100 that you want to display. The approach here is
更多推荐
具有多个表格连接的每组最多N组
发布评论