检查表格是否包含重叠的时间段。

Question

我有一个数据表包含两列FromDate和ToDate，它们的格式为字符串。我想检查一下表中是否存在重复记录。

From Date    To Date
----------------------      
9/01/2012    9/16/2012   
8/23/2012    8/24/2012   
8/25/2012    8/25/2012   
8/5/2012     8/6/2012    
8/26/2012    8/27/2012   
9/15/2012    9/23/2012

表格中存在重复记录，因为它们的日期范围映射为...

From Date       To Date      
----------------------      
9/01/2012    9/16/2012   
9/15/2012    9/23/2012

它应该返回false。

- palak mehta

---

@Tisho：感谢您的编辑。 - palak mehta

你是指“完全相同的日期”还是“重叠的时间段”？ - Mare Infinitus

@Mare Infinitus：如上所示的重叠跨度。 - palak mehta

请看一下我的答案。 - Mare Infinitus

Answer 1

var query = from row in dt.AsEnumerable()
            from row1 in dt.AsEnumerable()
            where
            (

                 (
                     DateTime.Parse(row1.Field<string>("fromDate")) >= DateTime.Parse(row.Field<string>("fromDate")) &&
                     DateTime.Parse(row1.Field<string>("fromDate")) <= DateTime.Parse(row.Field<string>("toDate"))
                 )
                 ||
                 (
                     DateTime.Parse(row1.Field<string>("toDate")) >= DateTime.Parse(row.Field<string>("fromDate")) &&
                     DateTime.Parse(row1.Field<string>("toDate")) <= DateTime.Parse(row.Field<string>("toDate"))
                 )
            )
            select new
            {
                fromDate = DateTime.Parse(row1.Field<string>("fromDate")),
                toDate = DateTime.Parse(row1.Field<string>("toDate"))
            };
//This lst contains the dates which are overlapping    
var lst = query.Distinct().ToList();

- Pravin Pawar

---

不错，但如果数据在datatable中是datetime格式，那就更好了，这样可以为每个记录节省DateTime.Parse x 2的时间。 - yogi

@ Pravin：你运行查询了吗？因为当我运行它时，变量lst显示为0，即使我有记录： 第一个FromDate：8/1/2012 第一个ToDate：8/2/2012 第二个FromDate：8/1/2012 第二个ToDate：8/11/2012 - palak mehta

确切地说，我也没有得到任何结果。 - Mare Infinitus

我展示的例子仅比较了fromDate和<，而没有使用<=。请根据您的需要更改where子句。 - Pravin Pawar

Answer 2

好的，那么自连接将有所帮助：

我有一个小类TimePeriod，只是为了满足您的需求。

public class TimePeriod
{
    public int Id;
    public DateTime FromDate { get; set; }

    public DateTime ToDate { get; set; }

    public static DateTime Parse(string date)
    {
        var dt = DateTime.Parse(date, 
        CultureInfo.CreateSpecificCulture("en-US"), DateTimeStyles.RoundtripKind);
        return dt;
    }
}

然后我有一些测试数据

var list = new List();

        list.Add(new TimePeriod() { Id = 1, FromDate = TimePeriod.Parse("9/01/2012"),  ToDate = TimePeriod.Parse("9/16/2012") });
        list.Add(new TimePeriod() { Id = 2, FromDate = TimePeriod.Parse("8/23/2012"), ToDate = TimePeriod.Parse("8/24/2012") });
        list.Add(new TimePeriod() { Id = 3, FromDate = TimePeriod.Parse("8/25/2012"), ToDate = TimePeriod.Parse("8/25/2012") });
        list.Add(new TimePeriod() { Id = 4, FromDate = TimePeriod.Parse("8/5/2012"), ToDate = TimePeriod.Parse("8/6/2012") });
        list.Add(new TimePeriod() { Id = 5, FromDate = TimePeriod.Parse("8/26/2012"), ToDate = TimePeriod.Parse("8/27/2012") });
        list.Add(new TimePeriod() { Id = 6, FromDate = TimePeriod.Parse("9/15/2012"), ToDate = TimePeriod.Parse("9/23/2012") });

这里是解决方案：（受OraNob的启发，感谢他）

var overlaps = from current in list
            from compare in list
            where
            (
            (compare.FromDate > current.FromDate &&
            compare.FromDate < current.ToDate) ||
            (compare.ToDate > current.FromDate &&
            compare.ToDate < current.ToDate)
            )
            select new
            {
                Id1 = current.Id,
                Id2 = compare.Id,
            };

也许您想要省略第二个ID（因为这里会有重复的1/6和6/1）

- Mare Infinitus

Answer 3

按照 ToDate、FromDate 进行排序（或构建一个索引数组来对 DataTable 进行排序）。从第二行或数组位置开始循环到结尾，检查 FromDate 是否小于等于前一项的 ToDate。将重叠的项目放入列表中。工作完成。

您还可以按照 FromDate、ToDate 进行排序，并执行类似的逻辑。

- ErikE

Answer 4

使用DataTable.Search()方法来查找您的DataTable中是否存在任何记录，这样您就可以强制确保记录的唯一性。

类似于这样：

string expression;
expression = "FromDate = #9/01/2012# AND ToDate = #9/16/2012#";
DataRow[] foundRows;

// Use the Select method to find all rows matching the filter.
foundRows = table.Select(expression);

if(foundRows.Length > 0)
     // Show duplicate message
else
    // Insert your new dates

欲了解更多请点击这里

- yogi

---

我没有任何表达式。记录已经在数据表中。 我想检查现有记录是否具有任何重复的范围值！！ - palak mehta

Answer 5

strong textDeclare @Table Table  
(  
    RowId Int Identity(1, 1) Not Null, 
      Id NChar(3) Not Null, 
      StartDate DATETIME Not Null, 
      EndDate DATETIME Not Null 
);  

Insert Into @Table (Id, StartDate, EndDate)  
Select 'id1', '20131210 10:10', '20131220 10:10' Union All  
Select 'id1', '20131211', '20131215' Union All  
Select 'id1', '20131201', '20131205' Union All  
Select 'id1', '20131206', '20131208' Union All  
Select 'id1', '20131225 10:10', '20131225 10:11'
Select *  
From @Table;  


With Overlaps (OverlapRowId, BaseRowId, OStart, OEnd, BStart, BEnd)  
As  
(  
        Select Overlap.RowId, Base.RowId, Overlap.StartDate, Overlap.EndDate, Base.StartDate, Base.EndDate 
        From @Table As Base  
        Inner Join @Table As Overlap On Overlap.Id = Base.Id  
        Where (((Overlap.StartDate > Base.StartDate) And (Overlap.StartDate < Base.EndDate))
          Or ((Overlap.StartDate = Base.StartDate) And (Overlap.EndDate > Base.EndDate))) 
          And (Base.RowId != Overlap.RowId) 
) 
-- Remove records that were found to cause overlap issues.  
Delete T  
From @Table As T  
Inner Join  
(  
        Select O.OverlapRowId   
        From Overlaps As O 
        Left Join Overlaps As Fp On Fp.OverlapRowId = O.BaseRowId  
        Where (Fp.OverlapRowId Is Null) 
) As SubQuery On SubQuery.OverlapRowId = T.RowId;  

-- Select the valid options.  
Select RowId, Id, StartDate, EndDate  
From @Table where StartDate<EndDate;  
Go

- Uthirasamy

---

非常感谢您帮助我解决这个问题。我还没有遇到过开始和结束日期相同的多条记录的情况。如果数据更改了ID，那将非常有帮助。谢谢！ - Uthirasamy

Answer 6

尝试解析“截止日期”列，并针对每个日期，在“起始日期”列中搜索任何早于该日期的具有较晚对应“截止日期”的日期。

- getedoi

---

一个嵌套循环（带有过滤的CROSS JOIN）。代价高昂！ - ErikE

Answer 7

如果您处理大型数据表，应该使用@ErikE的响应。虽然需要更多的代码行，但绝对是迄今为止最有效的。

如果它是小表格并且您更喜欢比较每两行，则可以使用其他人建议的方法。只需确保防止将行与自身进行比较，并在结果枚举中去除重复项即可。

var query = from x in list
            where list.Exists((y) => x != y &&
                                     x.FromDate <= y.ToDate &&
                                     y.FromDate <= x.ToDate)
            select x;

- Hamawi