問題描述

我正在努力尋找如何使用 LINQ 查詢或 LAMBDA 返回條件和的示例.我都是獨立編寫的，但是將 CASE 與 SUM 結合起來很麻煩.我很想作弊"并使用 SQL 視圖，但我想我會先問.我非常感謝任何建議.這是我要轉換的 SQL.

I'm struggling to find an example of how to return a conditional sum using a LINQ query or LAMBDA. I've written both independently but combining the CASE with SUM is vexing. I'm tempted to "cheat" and use a SQL view, but thought I'd ask first. I greatly appreciate any suggestions. Here's my SQL that I'm looking to convert.

SELECT     p.product_name, 
           SUM(CASE WHEN o.order_dt <= getdate() - 1 THEN o.quantity END) AS volume_1day, 
           SUM(CASE WHEN o.order_dt <= getdate() - 7 THEN o.quantity END) AS volume_7day, 
           SUM(CASE WHEN o.order_dt <= getdate() - 30 THEN o.quantity END) AS volume_30day, 
           SUM(o.quantity) AS volume_all
FROM       products p left outer join orders o on p.product_id = o.product_id
GROUP BY   p.product_name

推薦答案

這是一個使用 Northwinds 數據庫的示例.這將為您提供您期望的結果，但 SQL 與您的示例不匹配.

Here is an example using the Northwinds database. This will get you the results that you are expecting but the SQL won't match your example.

using (var context = new NorthwindEntities())
{
    DateTime volumn1Date = DateTime.Today.AddDays(-1);
    DateTime volumn7Date = DateTime.Today.AddDays(-7);
    DateTime volumn30Date = DateTime.Today.AddDays(-30);

    var query = from o in context.Order_Details
                group o by o.Product.ProductName into g
                select new
                {
                    ProductName = g.Key,
                    Volume1Day = g.Where(d => d.Order.OrderDate.Value <= volumn1Date)
                                  // cast to Int32? because if no records are found the result will be a null                                              
                                  .Sum(d => (Int32?) d.Quantity),
                    Volume7Day = g.Where(d => d.Order.OrderDate.Value <= volumn7Date)
                                  .Sum(d => (Int32?) d.Quantity),
                    Volume30Day = g.Where(d => d.Order.OrderDate.Value <= volumn30Date)
                                   .Sum(d => (Int32?) d.Quantity)
                };


    var list = query.ToList();
}

這篇關于帶有 CASE 語句和 SUM 函數的 LINQ 查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！