用于按状态获取计数的 Linq 查询
本文关键字:Linq 查询 状态 获取 用于 | 更新日期: 2023-09-27 18:37:17
我想在数据表中计算项目状态。
我的数据表看起来像
Date , Tkt Status
1/2/13 open
1/2/13 open
2/2/13 open
2/2/13 closed
2/2/13 closed
3/3/13 in-progress
4/3/13 closed
我想要另一个数据表,其中包含以下格式的数据
Date, Open, Closed, in-progress
1/2/13 2 0 0
2/2/13 1 2 0
3/3/13 0 0 1
4/3/13 0 1 0
我希望使用 Linq 完成它。
到目前为止我的尝试
dataQuery.Query = "Yes";
dataQuery.ViewFields = "<FieldRef Name='Created' /><FieldRef Name='tckPrty' /><FieldRef Name='tckStat' />";
dataQuery.ViewFieldsOnly = true;
tktData = tktList.GetItems(dataQuery).GetDataTable();
var newData = from row in tktData.AsEnumerable()
where groupDate(row.Field<string>("tckStat"))
group row by row.Field<string>("Created") into Status
orderby Status.Key
select new
{
key = Status.Key,
values = Status,
count = Status.Count()
};
foreach (var item in newData)
{
foreach (string s in tktStatus)
{
chartData.Rows.Add(item.key,item.count);
}
}
函数在这里
静态布尔组日期(弦技能) { 布尔值 = 假;
if (skill== "open")
{
value = true;
}
else
{
value = false;
}
return value;
}
显然,我们没有您的数据表可以使用,所以我从您的示例中构建了一个数组。
var lines = @"Date , Tkt Status
1/2/13 open
1/2/13 open
2/2/13 open
2/2/13 closed
2/2/13 closed
3/3/13 in-progress
4/3/13 closed".Split(new[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries).Skip(1).Select(l => l.Split(new[] { " " }, StringSplitOptions.RemoveEmptyEntries)).Select(x => new { Date = x[0], Status = x[1] }).ToArray();
然后,可以使用如下所示的 LINQ 对其进行分组:
var grouped = lines.GroupBy(l => l.Date).Select(g => new {
Date = g.Key,
Open = g.Count(l => l.Status == "open"),
Closed = g.Count(l => l.Status == "closed"),
InProgress = g.Count(l => l.Status == "in-progress")
}).ToArray();
输出:
Date Open Closed InProgress
1/2/13 2 0 0
2/2/13 1 2 0
3/3/13 0 0 1
4/3/13 0 1 0