这是我想要的Sql语句
SELECT
SUM(`IsRelease`) AS `ItemDownshelfCount`,
SUM(
CASE
WHEN `IsRelease` = 0
THEN 1
END
) AS `GoodsCount`
FROM
`eshop_goods_tb`
WHERE
`TenantId` = @__tid_0
AND `IsDelete` = 0;
这个是EFCore查询代码,
var query = await GetQueryableAsync(w => w.TenantId.Equals(tid) && w.IsDelete.Equals((int)YesOrNoEnum.No));
var result = query.GroupBy(x=>new { })
.Select(s => new GoodsOverview
{
ItemDownshelfCount = s.Where(w => w.IsRelease == (int)YesOrNoEnum.Yes).Count(),
GoodsCount = s.Where(w=>w.IsRelease == (int)YesOrNoEnum.No).Count()
});
return result.FirstOrDefault();
这是用EFCore拼接出来的Sql语句
主要纠结点是,有两层Select嵌套,这个可以优化么
SELECT CAST(COUNT(CASE
WHEN `t`.`IsRelease` = 1 THEN 1
END) AS SIGNED) AS `ItemDownshelfCount`, CAST(COUNT(CASE
WHEN `t`.`IsRelease` = 0 THEN 1
END) AS SIGNED) AS `GoodsCount`
FROM (
SELECT `e`.`IsRelease`, 1 AS `Key`
FROM `eshop_goods_tb` AS `e`
WHERE (`e`.`TenantId` = @__tid_0) AND (`e`.`IsDelete` = 0)
) AS `t`
GROUP BY `t`.`Key`
LIMIT 1
我想知道,这个IQueryable应该怎么写比较好?