c# - 在 Master 中对记录进行分组,但希望对 Details 进行计数
问题描述
请考虑以下表格:
掌握:
Id Year Season Flag City
------------------------------------------------------------------------------
1 2020 1 8000 Paris
2 2020 1 7000 Paris
3 2020 1 9000 London
4 2020 2 3000 Tokyo
5 2020 2 1000 Paris
6 2020 3 2000 Tokyo
7 2020 1 1000 London
8 2019 4 8000 Paris
9 2019 4 2000 Paris
细节:
Id MasterId Year Season CurrentFlag
--------------------------------------------------------------------------------------
1 8 2020 1 8000
2 9 2020 1 2500
3 8 2020 2 8100
4 1 2020 2 8000
5 2 2020 2 7500
6 2 2020 3 7500
7 3 2020 2 6000
8 4 2020 3 5000
9 7 2020 2 4000
考虑Master
作为我的记录生命周期的开始并在季节Details
中显示。CurrentFlag
现在我想要一个Linq
为以下结果带来此结果的查询(Year == 2010 and Season == 1)
:
City (Count Master All) (Count Master Flag<8000) (Count Details All) (Count Details Current Flag<8000)
---------------------------------------------------------------------------------------------------------
Paris 2 1 2 1
London 1 1 0 0
我写了这个查询:
var Records = (from m in master
join d in details
on m.Id equals d.MasterId into outerJoin
from d in outerJoin
where (m.Year == year && m.Dore == dore) ||
(d.Year == year && d.Dore == dore)
group m by new { m.City } into grp <--------
select new
{
City = grp.Key.City,
Count_Master_All = grp.Count(),
Count_Master_Below_8000 = grp.Where(o => o.Flag < 8000),
Count_Details_All = ???
Count_Details_Below_8000 = ???
}).ToList();
第一个问题是我只能对记录进行分组m
,因此我无法计算详细表的期望记录。我怎么能做到这一点?
谢谢
解决方案
稍后在查询中执行 Group
var Records = (from m in master
join d in details
on m.Id equals d.MasterId into outerJoin
from d in outerJoin
where (m.Year == year && m.Dore == dore) ||
(d.Year == year && d.Dore == dore)
select new {m = m, d = d})
.GroupBy(x => new { x.m.City })
.Select(grp => new
{
City = grp.Key.City,
Count_Master_All = grp.Count(),
Count_Master_Below_8000 = grp.Where(o => o.Flag < 8000),
Count_Details_All = grp.Count()
}).ToList();
推荐阅读
- android - Room db:数据库问题
- php - 当括号内的字母时,定制的 ucwords 失败
- r - 在 R 中转换数据对象
- javascript - 如何处理 Sequelize 嵌套/复合查询?
- r - 如何使用“by”在 data.table 中应用自定义函数并选择某些列
- php - 在带有 $SQLStatement 结构中的照片的照片的数据库中插入文本
- c# - 无法从派生自 ActionFilterAttribute 的类访问存储库(或 DbContext)
- python - 我一直尝试在 Python 上运行这个程序,但我一直收到相同的操作数错误。错误代码来自第 10 行
- android - ActionBarDrawerToggle 抽屉不会在按钮按下时滑出
- javascript - 节点 + 笑话 + 模拟事件处理程序回调