获取最新的十大最畅销的产品 [英] Get Top 10 most sold products between date

问题描述

我想在用户指定的日期返回前10名最畅销的产品（最多数量）。

I would like to return the top 10 most sold products (most quantity) during dates specified by the user.

我从数据库表：

订单

OrderId | OrderDate

Order_Products

ProductID | OrderID | Quantity

产品

ProductID | ProductName | ProductDescription

code：

Code:

return (from product in this.Entities.Products
        from orderProduct in this.Entities.Order_Product 
        from order in this.Entities.Orders
        where order.OrderId = orderProduct.ProductID && orderProduct.ProductID == product.ProductID
        where (order.OrderDate >= date1 &&  <= date2)
        select product).OrderByAscending(COUNT(Quantity)).Distinct().Take(10);

code到现在。

code up to now.

推荐答案

我能想到的两种方法可以做到这一点。

I can think of two ways to do this

这确实一个子查询，对每个产品进行总结日期范围之间出售的数量。这似乎是最明确的给我，但可能不执行，以及

This does a sub query for each product to sum up the quantities sold between the date range. This seems the most clear to me, but might not perform as well

  var query = 
   (from p in Entities.Products
    let totalQuantity = ( from op in Entities.Order_Product
                          join o in Entities.Orders on op.OrderID equals o.OrderId
                          where op.ProductID == p.ProductID && o.OrderDate >= date1 && o.OrderDate <= date2
                          select op.Quantity ).Sum()
    where totalQuantity > 0
    orderby totalQuantity descending
    select p).Take(10);

或者作为其获得的所有订单的日期范围内的单个查询，将它们由他们的产品，并总结的数量。

Or as a single query which gets all orders in the date range, groups them up by their product, and sums the quantities.

  var query = 
   (from p in Entities.Products
    join op in Entities.Order_Product on p.ProductID equals op.ProductID
    join o in Entities.Orders on op.OrderID equals o.OrderId
    where o.OrderDate >= date1 && o.OrderDate <= date2
    select new { Product = p, Quantity = op.Quantity } into productQty
    group productQty by productQty.Product into pg
    let totalQuantity = pg.Sum(prod => prod.Quantity)
    orderby totalQuantity descending
    select pg.Key).Take(10);

这篇关于获取最新的十大最畅销的产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！