我在根据不同的条件过滤数据表时遇到问题。我知道第一个where-子句
where row.Field<TimeSpan>("DateDifference") >= TimeSpan.Zero
这就是为什么不满足第三个标准的原因。有没有办法更改我的查询以满足所有要求？
- 日期差异应为正数。
- 应选择最小的日期差异。
所有库存更改都必须在结果中。所以一个负面如果没有正的日期差异，则允许日期差异。这应选择最小的负日期差异。
ArticleNo Article Price PriceSet InventoryChange DateDifference StockDifference 1 Article A 10 01.01.2012 02.01.2012 1 -2 1 Article A 11 01.06.2012 02.01.2012 -151 -2 2 Article B 14 01.01.2012 05.01.2012 4 1 2 Article B 14 01.01.2012 04.10.2012 277 -3 2 Article B 13 01.06.2012 05.01.2012 -148 1 2 Article B 13 01.06.2012 04.10.2012 125 -3 3 Article C 144 01.04.2012 28.02.2012 -33 -1 3 Article C 124 01.05.2012 28.02.2012 -63 -1 My result: 1 Article A 10 01.01.2012 02.01.2012 1 -2 2 Article B 14 01.01.2012 05.01.2012 4 1 2 Article B 13 01.06.2012 04.10.2012 125 -3 What I want to have is a table where the last row, where there is no positive DateDifference, is added. The row with the smallest DateDifference should be selected: 1 Article A 10 01.01.2012 02.01.2012 1 -2 2 Article B 14 01.01.2012 05.01.2012 4 1 2 Article B 13 01.06.2012 04.10.2012 125 -3 3 Article C 144 01.04.2012 28.02.2012 -33 -1
到目前为止我的查询：
var query = from row in InventoryChanges.AsEnumerable()
where row.Field<TimeSpan>("DateDifference") >= TimeSpan.Zero
group row by new
{
ArticleNo = row.Field<Int32>("ArticleNo"),
Article = row.Field<String>("Article"),
InventoryChange = row.Field<DateTime>("InventoryChange"),
StockDifference = row.Field<Int32>("StockDifference")
}
into grp
select new
{
ArticleNo = grp.Key.ArticleNo,
Article = grp.Key.Article,
InventoryChange = grp.Key.InventoryChange,
PriceSet = grp.Where(r => r.Field<TimeSpan>("DateDifference") == grp.Select(min => min.Field<TimeSpan>("DateDifference")).Min())
.Select(r => r.Field<DateTime>("PriceSet")).FirstOrDefault(),
DateDifference = grp.Select(r => r.Field<TimeSpan>("DateDifference")).Min(),
StockDifference = grp.Key.StockDifference,
Price = grp.Where(r => r.Field<TimeSpan>("DateDifference") == grp.Select(min => min.Field<TimeSpan>("DateDifference")).Min())
.Select(r => r.Field<Decimal>("Price")).FirstOrDefault(),
};
任何帮助不胜感激！
DataTable InventoryChanges = new DataTable("InventoryChanges");
InventoryChanges.Columns.Add("ArticleNo", typeof(Int32));
InventoryChanges.Columns.Add("Article", typeof(String));
InventoryChanges.Columns.Add("Price", typeof(Decimal));
InventoryChanges.Columns.Add("PriceSet", typeof(DateTime));
InventoryChanges.Columns.Add("InventoryChange", typeof(DateTime));
InventoryChanges.Columns.Add("DateDifference", typeof(TimeSpan));
InventoryChanges.Columns.Add("StockDifference", typeof(Int32));
DataRow dr = InventoryChanges.NewRow();
dr.ItemArray = new object[] { 1, "Article A", 10, new DateTime(2012, 1, 1), new DateTime(2012, 1, 2), new TimeSpan(1, 0, 0, 0), -2 };
InventoryChanges.Rows.Add(dr);
dr = InventoryChanges.NewRow();
dr.ItemArray = new object[] { 1, "Article A", 11, new DateTime(2012, 6, 1), new DateTime(2012, 1, 2), new TimeSpan(-151, 0, 0, 0), -2 };
InventoryChanges.Rows.Add(dr);
dr = InventoryChanges.NewRow();
dr.ItemArray = new object[] { 2, "Article B", 14, new DateTime(2012, 1, 1), new DateTime(2012, 1, 5), new TimeSpan(4, 0, 0, 0), 1 };
InventoryChanges.Rows.Add(dr);
dr = InventoryChanges.NewRow();
dr.ItemArray = new object[] { 2, "Article B", 14, new DateTime(2012, 1, 1), new DateTime(2012, 10, 4), new TimeSpan(277, 0, 0, 0), -3 };
InventoryChanges.Rows.Add(dr);
dr = InventoryChanges.NewRow();
dr.ItemArray = new object[] { 2, "Article B", 13, new DateTime(2012, 6, 1), new DateTime(2012, 1, 5), new TimeSpan(-148, 0, 0, 0), 1 };
InventoryChanges.Rows.Add(dr);
dr = InventoryChanges.NewRow();
dr.ItemArray = new object[] { 2, "Article B", 13, new DateTime(2012, 6, 1), new DateTime(2012, 10, 4), new TimeSpan(125, 0, 0, 0), -3 };
InventoryChanges.Rows.Add(dr);
dr = InventoryChanges.NewRow();
dr.ItemArray = new object[] { 3, "Article C", 144, new DateTime(2012, 4, 1), new DateTime(2012, 2, 28), new TimeSpan(-33, 0, 0, 0), -1 };
InventoryChanges.Rows.Add(dr);
dr = InventoryChanges.NewRow();
dr.ItemArray = new object[] { 3, "Article C", 124, new DateTime(2012, 5, 1), new DateTime(2012, 2, 28), new TimeSpan(-63, 0, 0, 0), -1 };
InventoryChanges.Rows.Add(dr);
也许有更优雅的方法，但这应该有效：
var query = InventoryChanges.AsEnumerable()
.GroupBy(r => new
{
ArticleNo = r.Field<Int32>("ArticleNo"),
Article = r.Field<String>("Article"),
InventoryChange = r.Field<DateTime>("InventoryChange"),
StockDifference = r.Field<Int32>("StockDifference")
})
.Select(grp =>
{
IEnumerable<DataRow> rows = grp;
bool anyPositiveDateDiff = grp.Any(r => r.Field<TimeSpan>("DateDifference") >= TimeSpan.Zero);
if (anyPositiveDateDiff)
rows = grp.Where(r => r.Field<TimeSpan>("DateDifference") >= TimeSpan.Zero);
var firstRow = rows
.OrderBy(r => r.Field<TimeSpan>("DateDifference").Duration()).First();
return new
{
ArticleNo = grp.Key.ArticleNo,
Article = grp.Key.Article,
InventoryChange = grp.Key.InventoryChange,
PriceSet = firstRow.Field<DateTime>("PriceSet"),
DateDifference = rows.Min(r => r.Field<TimeSpan>("DateDifference")),
StockDifference = grp.Key.StockDifference,
Price = firstRow.Field<Decimal>("Price")
};
});
我正在检查组中是否有bool anyPositiveDateDiff时间跨度为正的行。然后，我将组的行替换为正的时间跨度行。
另请注意，我已经简化并改进了您创建匿名类型的选择中的子查询。
编辑 这是根据您提供的示例数据进行上述查询的结果：
{ ArticleNo = 2, Article = Article B, InventoryChange = 05.01.2012 00:00:00, PriceSet = 01.01.2012 00:00:00, DateDifference = 4.00:00:00, StockDifference = 1, Price = 14 }
{ ArticleNo = 2, Article = Article B, InventoryChange = 04.10.2012 00:00:00, PriceSet = 01.06.2012 00:00:00, DateDifference = 125.00:00:00, StockDifference = -3, Price = 13 }
{ ArticleNo = 3, Article = Article C, InventoryChange = 28.02.2012 00:00:00, PriceSet = 01.04.2012 00:00:00, DateDifference = -63.00:00:00, StockDifference = -1, Price = 144 }