我正在尝试选择行具有"ID"字段等于"ID"值并且"Date"字段等于另一个表"dv"中的日期字段"Log Date"的记录。
问题是"foundID"中返回的记录没有以AND方式处理行，而是将其处理为OR。
for (int id = 0; id < 200; id++)
{
var foundID = from r in dv.AsEnumerable()
orderby r.Field<string>("Log Date")
where r.Field<int>("User ID") == id
select r;
foreach (var row in foundID)
{
var foundDate = overtime.Select("ID = '" + id
+ "' AND 'Log Date' = '"+row.Field<string>("Log Date")+"'");
if(foundDate.Count() == 0){
Rowq["ID"] = row.Field<int>("User ID");
Rowq["Date"] = row.Field<string>("Log Date");
overtime.Rows.Add(Rowq);
Rowq = overtime.NewRow();
}
else {
continue;
}
}
id++;
}
输入"dv"表如下所示：
User ID Name Log Date Log Time FKey
0000000002 Name1 2014/10/16 09:03:13 F1
0000000002 Name1 2014/10/16 17:02:20 F2
0000000002 Name1 2014/10/18 08:38:42 F1
0000000002 Name1 2014/10/18 16:55:02 F2
0000000002 Name1 2014/10/19 09:05:21 F1
0000000004 Name2 2014/10/01 00:07:09 F2
0000000004 Name2 2014/10/01 15:46:49 F1
0000000004 Name2 2014/10/02 00:09:52 F2
输出"超时"必须如下所示：
ID Name Date F1 F2 None
2 Name1 2014/10/18 time time time
2 Name1 2014/10/19 time time time
4 Name2 2014/10/01 time time time
4 Name2 2014/10/01 time time time
4 Name2 2014/10/02 time time time
etc..
正如您所注意到的，每个ID都有几个记录，每个日期一个。但是输出表"加班"为每个ID返回两条记录，其中有一个随机日期。
我在第一篇文章中解决了一个关于增量的问题，但现在它仍然为每个ID显示两行。我想将每一行与其具有相同ID和日期的计数器部分行合并。的功能var foundDate=加班。选择（"ID='"+ID+"'AND'日志日期'='"+行.Field（"日志日期"）+"'"）；虽然加班表包含ID和Date 的匹配记录，但不返回记录
首先，它应该是
var foundDate = overtime.Select("ID = '" + id
+ "' AND [Log Date] = '"+row.Field<string>("Log Date")+"'");
请注意，[记录日期]而不是"记录日期"
其次，您在记录一个超时后递增id，但随后继续插入与前一个id显式匹配的行。
要么，递增是错误的，要么你应该在递增后跳出循环。
看看下面，它在加班表中提供了正确的数据
void Main()
{
var overtime = new DataTable();
overtime.Columns.Add("ID", typeof(int));
overtime.Columns.Add("Log Date", typeof(string));
overtime.Columns.Add("F1", typeof(string));
overtime.Columns.Add("F2", typeof(string));
var dv = new DataTable();
dv.Columns.Add("User ID", typeof(int));
dv.Columns.Add("Log Date", typeof(string));
dv.Columns.Add("Type", typeof(string));
dv.Columns.Add("Time", typeof(string));
var row = dv.NewRow();
row["User ID"] = 0000000002;
row["Log Date"] = "2014/10/16";
row["Type"] = "F1";
row["Time"] = "09:03:13";
dv.Rows.Add(row);
row = dv.NewRow();
row["User ID"] = 0000000002;
row["Log Date"] = "2014/10/16";
row["Type"] = "F2";
row["Time"] = "17:02:20";
dv.Rows.Add(row);
row = dv.NewRow();
row["User ID"] = 0000000002;
row["Log Date"] = "2014/10/18";
row["Type"] = "F1";
row["Time"] = "08:38:42";
dv.Rows.Add(row);
row = dv.NewRow();
row["User ID"] = 0000000002;
row["Log Date"] = "2014/10/19";
row["Type"] = "F2";
row["Time"] = "16:55:02";
dv.Rows.Add(row);
row = dv.NewRow();
row["User ID"] = 0000000002;
row["Log Date"] = "2014/10/19";
row["Type"] = "F1";
row["Time"] = "09:05:21";
dv.Rows.Add(row);
row = dv.NewRow();
row["User ID"] = 0000000004;
row["Log Date"] = "2014/10/01";
row["Type"] = "F2";
row["Time"] = "00:07:09";
dv.Rows.Add(row);
row = dv.NewRow();
row["User ID"] = 0000000004;
row["Log Date"] = "2014/10/01";
row["Type"] = "F1";
row["Time"] = "15:46:49";
dv.Rows.Add(row);
row = dv.NewRow();
row["User ID"] = 0000000004;
row["Log Date"] = "2014/10/02";
row["Type"] = "F2";
row["Time"] = "00:09:52 ";
dv.Rows.Add(row);
foreach(var personGrouping in dv.Rows.Cast<DataRow>().GroupBy(r => r.Field<int>("User ID")))
{
foreach(var dayGrouping in personGrouping.GroupBy(r => r.Field<string>("Log Date")))
{
//Now we need to find F1 and F2
var orderedTime = dayGrouping.OrderBy(dg => dg.Field<string>("Time")).ToList();
for (var i = 0; i < orderedTime.Count; i++)
{
var f1 = orderedTime[i];
if (orderedTime.Count <= i + 1) //Either F2 doesn't exist, or it exists on another date
continue;
var f2 = orderedTime[i + 1];
if (!overtime.Rows.Cast<DataRow>().Any (dr => dr.Field<int>("ID") == personGrouping.Key && dr.Field<string>("Log Date") == dayGrouping.Key))
{
row = overtime.NewRow();
row["ID"] = personGrouping.Key;
row["Log Date"] = dayGrouping.Key;
row["F1"] = f1.Field<string>("Time");
row["F2"] = f2.Field<string>("Time");
overtime.Rows.Add(row);
}
}
}
}
overtime.Dump();
}
这会产生输出：
2 2014/10/16 09:03:13 17:02:20
2 2014/10/19 09:05:21 16:55:02
4 2014/10/01 00:07:09 15:46:49
如果F2落在第二天（发生在您的示例数据中）：
0000000002 Name1 2014/10/19 09:05:21 F1
0000000004 Name2 2014/10/01 00:07:09 F2
未添加条目。您必须根据您的业务需求修改以上内容