不同的数据表中的多个列 [英] Distinct on multiple columns in datatable

问题描述

试图做一个SQL查询（ SELECT DISTINCT（第一，第二），第三FROM表）会做，但我的LINQ和DataTable这样做。

Trying to do what a SQL query (SELECT DISTINCT (first,second),third FROM table) would do but I am doing it with LINQ and a datatable.

修改

SQL应该像一个MySQL

SQL should look like a Mysql

选择第一，第二，第三
 FROM表
 由第一组，第二个

DataTable secondTable = new DataTable();
secondTable.Columns.Add("name", typeof(string));
secondTable.Columns.Add("date", typeof(string));
secondTable.Columns.Add("clockIn", typeof(string));
secondTable.Columns.Add("clockOut", typeof(string));

var t4 = (from a in firstTable.AsEnumerable()
select new
{
    name = a.Field<string>("name"),
    date = a.Field<string>("date"),
    clockIn = a.Field<string>("clockIn"),
    clockOut = a.Field<string>("clockOut")
}).Distinct();

var t5 = (from a in firstTable.AsEnumerable()
select new
{
    name = a.Field<string>("name"),
    date = a.Field<string>("date")
}).Distinct();

var t6 = (from d in t5
          join a in t4
          on new
          {
              d.name,
              d.date
          }
          equals new
          {
              a.name,
              a.date
          }
          select secondTable.LoadDataRow(
          new object[]
          {
               d.name,d.date,a.clockIn,a.clockOut
          }, false)).ToList();

ViewBag.Data = secondTable;

这是什么code正在做的是，它是在<$ C $加入 T4 和 T5 C> T6 没有排除。虽然我的愿望是 T4 是在 T5 present所有行应<$ C $加盟C> T6 （名称，日期）的基础上，并从 T5所有行的指不以<$ C $存在C> T4 应排除在外。任何人都可以请帮助？

What this code is doing is, it is joining t4 and t5 in t6 with no exclusions. While what I desire is all rows from t4 that are present in t5 should join with t6 on the basis on (name, date) AND all rows from t5that don't exist in t4 should be excluded. Can anyone please help?

推荐答案

从你的意见，你可能只是在需要的字段组，采取分组的结果。

From your comments, you may just group by the desired fields, and take any of the grouped result.

您可以通过clockin或时钟输出为了得到一个不那么随机的结果。

You may order by clockin or clockout to get a less "random" result.

var t6 = firstTable.AsEnumerable()
                   .GroupBy(a => new {
                                     name = a.Field<string>("name"),
                                     date = a.Field<string>("date")
                                     }
                            )
                   .Select(g => g.First())
                   //or Select(g => g.OrderBy(a => a.Field<string>("clockIn")).First()
                   .ToList();

这篇关于不同的数据表中的多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！