SQL联接表和SUM值

本文关键字：SUM SQL | 更新日期: 2023-09-27 18:22:19

我有两个表：

产品和库存

我目前选择了产品的内容，并对库存表进行了第二次调用，以总结productID的总库存，这非常缓慢。

我想创建一个对数据库的调用，以获取Product表的内容，并对库存表（链接到ProductID）中的StockInHand总数求和，如下所示：

如果有人能向我展示如何成功地加入表格，并在对ProductID的同一次调用中对Stock表格的QtyInHand进行求和，我将非常棒。

我的原始代码：

            var query = from products in data.Products
                        where products.Deleted == false
                        orderby products.FullPath
                        select new
                        {
                            ProductID = products.ProductID,
                            Description = products.Description,
                            Price = products.RetailPrice ?? 0,
>>>> VERY SLOW!             StockLevel = cProducts.GetStockTotalForProduct(products.ProductID), 
                            FullPath = products.FullPath
                        };
            if (query != null)
            {
                dgv.DataSource = query;
            }

我知道我需要加入表，但我不确定使用LINQ:的语法

    var query = 
                from product in data.Products
                join stock in data.ProductStocks on product.ProductID equals stock.ProductID
 DO SOMETHING CLEVER HERE!!
                select new
                {
                    ProductID = product.ProductID,
                    Description = product.Description,
                    Price = product.RetailPrice ?? 0,
                    StockLevel = >>>>>>>> CALL TO THE OTHER TABLE IS VERY SLOW,
                    FullPath = products.FullPath
                };
            if (query != null)
            {
                dgv.DataSource = query;
            }

SQL联接表和SUM值

我认为您正在寻找一个group by来完成总和：

var query = from p in data.Products
            join s in data.ProductStocks on p.ProductID equals s.ProductID
            group s by p into g
            select new {
                ProductID = g.Key.ProductID,
                Description = g.Key.Description,
                Price = g.Key.Price ?? 0,
                FullPath = g.Key.FullPath,
                StockLevel = g.Sum(s => s.StockInHand)
            };

我认为这应该有效：

 var query = from product in data.Products
                join stock in data.ProductStocks on product.ProductID equals stock.ProductID
                select new
                {
                    ProductID = product.ProductID,
                    Description = product.Description,
                    Price = product.RetailPrice ?? 0,
                    StockLevel = stock.StockLevel,
                    FullPath = products.FullPath
                };
相关文章：