如何以另一个列为条件从DataTable对象中选择不同的列组合？ [英] How can I select distinct column combinations from a DataTable object with another column as a condition?

问题描述

我正在使用C＃2010。

I'm using C# 2010.

我有一个正在使用的DataTable对象，它具有以下结构（并填充有示例数据）：

I have a DataTable object I'm working with which has the following structure (and is filled with sample data):

"name"    "ID"    "hiredate"    "termdate"
Bobby     1        5/1/2011       7/1/2011
Peggy     2        5/1/2011
Jenny     3        5/2/2011
Jenny     3        5/2/2013
Jenny     3        5/2/2011       6/1/2011
Peggy     2        5/1/2011

我要过滤此数据表以仅保留（ ID， hiredate）组合。此外，如您所见，并非每个人都有 termdate值。在决定保留哪个不同的（ ID， hiredate）组合时，我想保留一个也具有 termdate的组合。如果其中任何一个都不存在 termdate，则丢弃哪个都不重要。

I want to filter this DataTable to keep only distinct ("ID","hiredate") combinations. Furthermore, as you can see, not everyone has a "termdate" value. When deciding which distinct ("ID","hiredate") combination to keep, I want to keep the one that also has a "termdate". If there's no "termdate" in any of them it doesn't matter which one is discarded.

因此，执行此操作后的结果表将是：

So the resulting table after doing this would be:

"name"    "ID"    "hiredate"    "termdate"
Bobby     1        5/1/2011       7/1/2011
Peggy     2        5/1/2011
Jenny     3        5/2/2013
Jenny     3        5/2/2011       6/1/2011

Jenny有两个条目，因为她出现了两个不同的 hiredate值，并且其中一个也被复制了-删除了没有 termdate的条目。

Jenny has two entries because she appeared with two different "hiredate" values, and one of them was also duplicated - the entry without the "termdate" was removed.

关于如何在C＃中执行此操作的任何建议？同样，我正在使用DataTable对象。我仍然需要保留名称和任期字段-如果没有，那么我可以获得一个不同的（ ID，受雇）列表，但确实需要保留它们。

Any suggestions for how to do this in C#? Again, I'm using a DataTable object. I still need to keep the "name" and "termdate" fields - if I didn't, then I was able to get a distinct ("ID","hiredate") list, but they really need to be retained.

感谢您的帮助！

推荐答案

我可能会这样做，dt是您的原始数据表-

I would probably do something like this, dt is your original datatable -

这应涵盖由于数据视图排序而引起的所有可能，保留的日期列表允许为以下情况添加多个雇用日期

This should cover all eventualities due to the ordering of the data view, the date list which is maintained allows the addition of multiple hire dates for a user.

            DataView dv = new DataView(dt);
            dv.Sort = "ID ASC, HireDate DESC, TermDate DESC";

            string lastID = "0";
            List<DateTime> addedHireDatesForUser = new List<DateTime>();

            foreach (DataRowView drv in dv)
            {
                if (drv["ID"].ToString() != lastID)
                {
                    addedHireDatesForUser = new List<DateTime>();
                    addedHireDatesForUser.Add(DateTime.Parse(drv["HireDate"].ToString()));

                    // NEXT ID, ADD ROW TO NEW DATATABLE
                }
                else if (!addedHireDatesForUser.Contains(DateTime.Parse(drv["HireDate"].ToString())))
                {
                    addedHireDatesForUser.Add(DateTime.Parse(drv["HireDate"].ToString());

                    // NEXT DATE, ADD ROW TO NEW DATATABLE
                }

                lastID = drv["ID"].ToString();
            }

这篇关于如何以另一个列为条件从DataTable对象中选择不同的列组合？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！