GroupJoin

场景：

两个库存表，一个是入库明细，一个是出库明细，两个库存表汇总得出剩余可用库存数

数据准备：

入库明细表

CREATE TABLE InStockInventoryEnterItem (
Id int NOT NULL AUTO_INCREMENT,
Qty decimal(18,8) DEFAULT NULL,
InStockTime datetime DEFAULT NULL,
PRIMARY KEY (Id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

INSERT INTO `InStockInventoryEnterItem` VALUES (1, 1000.00000000, '2023-02-20 11:30:19');
INSERT INTO `InStockInventoryEnterItem` VALUES (2, 2000.00000000, '2023-02-22 11:30:41');
INSERT INTO `InStockInventoryEnterItem` VALUES (3, 500.00000000, '2023-02-23 11:30:56');

出库明细表

CREATE TABLE `InStockInventoryExportItem`  (
  `Id` int(0) NOT NULL,
  `Qty` decimal(18, 8) NULL DEFAULT NULL,
  `OutStockTime` datetime(0) NULL DEFAULT NULL,
  `EnterItemId` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `InStockInventoryExportItem` VALUES (1, 200.00000000, '2023-02-23 11:31:09', 1);
INSERT INTO `InStockInventoryExportItem` VALUES (3, 600.00000000, '2023-02-22 11:31:43', 2);
INSERT INTO `InStockInventoryExportItem` VALUES (4, 10.00000000, '2023-03-11 13:34:41', 2);

Linq

var gExportQueryable = InStockInventoryExportItems.GroupBy(p => p.EnterItemId).
Select(p => new
{
	EnterItemId = p.Key,
	OutTotalQty = p.Sum(p => p.Qty),
	LastOutStockTime = p.Max(p => p.OutStockTime)
});
var query = InStockInventoryEnterItems.GroupJoin(gExportQueryable, i => i.Id, o => o.EnterItemId, (i, o) => new
{
	Export = o,
	Enter = i
}).SelectMany(p => p.Export.DefaultIfEmpty(), (s, i) => new
{
	EnterItemId = s.Enter.Id,
	InStockQty = s.Enter.Qty,
	InStockTime = s.Enter.InStockTime,
	OutTotalQty = i.OutTotalQty == null ? 0 : i.OutTotalQty,
	LastOutStockTime = i.LastOutStockTime,
	AvailableQty = s.Enter.Qty - (i.OutTotalQty == null ? 0 : i.OutTotalQty)
});
Console.WriteLine(query.ToList());

//对应SQL
SELECT `i`.`Id` AS `EnterItemId`, `i`.`Qty` AS `InStockQty`, `i`.`InStockTime`, CASE
WHEN `t`.`OutTotalQty` IS NULL THEN 0.0
ELSE `t`.`OutTotalQty`
END AS `OutTotalQty`, `t`.`LastOutStockTime`, CASE
WHEN `t`.`OutTotalQty` IS NULL THEN 0.0
ELSE `i`.`Qty` - `t`.`OutTotalQty`
END AS `AvailableQty`
FROM `InStockInventoryEnterItem` AS `i`
LEFT JOIN (
SELECT `i0`.`EnterItemId`, COALESCE(SUM(`i0`.`Qty`), 0.0) AS `OutTotalQty`, MAX(`i0`.`OutStockTime`) AS `LastOutStockTime`
FROM `InStockInventoryExportItem` AS `i0`
GROUP BY `i0`.`EnterItemId`
) AS `t` ON `i`.`Id` = `t`.`EnterItemId`