子查询+Sum 出错 返回
William 发布于2018/10/7
代码：
return DB.FullClient.Queryable<ContractOrders, Contracts>((co, cs) => new object[]{
JoinType.Inner,co.ContractId==cs.Id
})
.WhereIF(!string.IsNullOrEmpty(customerId), (co, cs) => cs.CustomerId == customerId)
.Select((co, cs) => new ContractOrdersP
{
Code = co.Code,
Id = co.Id,
CreateTime = co.CreateTime,
ContractId = co.ContractId,
Remark = co.Remark,
Status = co.Status,
Contracts = cs,
TotalPrice = SqlFunc.Subqueryable<ProductConfigs>()
.Where(r => r.ContractOrderId == co.Id).GroupBy(r => r.ContractOrderId)
.Sum(r => (r.DiscountPrice * r.Quantity)),
})
.ToPageList(current, rowCount);
生成的SQL：
SELECT
`co`.`Code` AS `Code` ,
`co`.`Id` AS `Id` ,
`co`.`CreateTime` AS `CreateTime` ,
`co`.`ContractId` AS `ContractId` ,
`co`.`Remark` AS `Remark` ,
`co`.`Status` AS `Status` ,
`cs`.`Id` AS `Contracts.Id` ,
`cs`.`Code` AS `Contracts.Code` ,
`cs`.`Title` AS `Contracts.Title` ,
`cs`.`ActivateDate` AS `Contracts.ActivateDate` ,
`cs`.`EndDate` AS `Contracts.EndDate` ,
`cs`.`CreateTime` AS `Contracts.CreateTime` ,
`cs`.`CustomerId` AS `Contracts.CustomerId` ,
`cs`.`CustomerName` AS `Contracts.CustomerName` ,
`cs`.`ContractingParty` AS `Contracts.ContractingParty` ,
`cs`.`ExpireDate` AS `Contracts.ExpireDate` ,
`cs`.`SettleDate` AS `Contracts.SettleDate` ,
`cs`.`Status` AS `Contracts.Status` ,
`cs`.`CensorDate` AS `Contracts.CensorDate` ,
(SELECT SUM(({0}`DiscountPrice``Quantity`)) FROM `ProductConfigs` WHERE ( `ContractOrderId` = `co`.`Id` ) GROUP BY `ContractOrderId`) AS `TotalPrice`
FROM `ContractOrders` co Inner JOIN `Contracts` cs ON ( `co`.`ContractId` = `cs`.`Id` )
WHERE ( `cs`.`CustomerId` = @CustomerId0 ) LIMIT 0,2
错误信息：
2018-10-07 16:13:54.5904|1|ERROR|Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware|An unhandled exception has occurred while executing the request. MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0}`DiscountPrice``Quantity`)) FROM `ProductConfigs` WHERE ( `ContractOrderId` = ' at line 1
热忱回答（2）
-
fate stay night VIP0
2018/10/8近期会更新
0 回复 -
Wellis VIP0
2018/10/12我自己已经找到解决办法了,分享一下
var query = DB.FullClient.Queryable<ContractOrders, Contracts>((co, cs) => new object[]{
JoinType.Inner,co.ContractId==cs.Id
})
.WhereIF(!string.IsNullOrEmpty(customerId), (co, cs) => cs.CustomerId == customerId)
.WhereIF(startDate.HasValue, (co, cs) => cs.ActivateDate >= startDate.Value)
.WhereIF(endDate.HasValue, (co, cs) => cs.ActivateDate <= endDate.Value.AddDays(1))
.WhereIF(status.HasValue, co => co.Status == status.Value)
.Select((co, cs) => new ContractOrdersP<Contracts>
{
Code = co.Code,
Id = co.Id,
CreateTime = co.CreateTime,
ContractId = co.ContractId,
Remark = co.Remark,
Status = co.Status,
Contracts = cs,
TotalPrice = SqlFunc.Subqueryable<ProductConfigs>()
.Where(r => r.ContractOrderId == co.Id)
.GroupBy(r => r.ContractOrderId)
.Select(r => SqlFunc.AggregateSum(r.Quantity * r.DiscountPrice))
});
用这个函数可以解决, SqlSugar作者 定义了AggregateSum 这种方法, 一般人还真不知道
0 回复