C# 如何在 DataTable.Select(Expression) 中使用 SELECT GROUP BY？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19407387/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I use SELECT GROUP BY in DataTable.Select(Expression)?
提问by J - C Sharper
I try to remove the duplicate rows by select a first row from every group. For Example
我尝试通过从每个组中选择第一行来删除重复的行。例如
PK Col1 Col2
1 A B
2 A B
3 C C
4 C C
I want a return:
我要退货：
PK Col1 Col2
1 A B
3 C C
I tried following code but it didn't work:
我尝试了以下代码，但没有用：
DataTable dt = GetSampleDataTable(); //Get the table above.
dt = dt.Select("SELECT MIN(PK), Col1, Col2 GROUP BY Col1, Col2);
采纳答案by D Stanley
DataTable's Selectmethod only supports simple filtering expressions like {field} = {value}. It does not support complex expressions, let alone SQL/Linq statements.
DataTable的Select方法只支持简单的过滤表达式，如{field} = {value}. 它不支持复杂的表达式，更不用说 SQL/Linq 语句了。
You can, however, use Linq extension methods to extract a collection of DataRows then create a newDataTable.
但是，您可以使用 Linq 扩展方法来提取DataRows的集合，然后创建一个新的DataTable.
dt = dt.AsEnumerable()
.GroupBy(r => new {Col1 = r["Col1"], Col2 = r["Col2"]})
.Select(g => g.OrderBy(r => r["PK"]).First())
.CopyToDataTable();
回答by J - C Sharper
dt = dt.AsEnumerable().GroupBy(r => r.Field<int>("ID")).Select(g => g.First()).CopyToDataTable();
回答by Alfred Wallace
This solution sort by Col1 and group by Col2. Then extract value of Col2 and display it in a mbox.
此解决方案按 Col1 排序，按 Col2 分组。然后提取 Col2 的值并将其显示在 mbox 中。
var grouped = from DataRow dr in dt.Rows orderby dr["Col1"] group dr by dr["Col2"];
string x = "";
foreach (var k in grouped) x += (string)(k.ElementAt(0)["Col2"]) + Environment.NewLine;
MessageBox.Show(x);
回答by JJS
Tim Schmelter's answer https://stackoverflow.com/a/8472044/26877
蒂姆施梅尔特的回答https://stackoverflow.com/a/8472044/26877
public DataTable GroupBy(string i_sGroupByColumn, string i_sAggregateColumn, DataTable i_dSourceTable)
{
DataView dv = new DataView(i_dSourceTable);
//getting distinct values for group column
DataTable dtGroup = dv.ToTable(true, new string[] { i_sGroupByColumn });
//adding column for the row count
dtGroup.Columns.Add("Count", typeof(int));
//looping thru distinct values for the group, counting
foreach (DataRow dr in dtGroup.Rows) {
dr["Count"] = i_dSourceTable.Compute("Count(" + i_sAggregateColumn + ")", i_sGroupByColumn + " = '" + dr[i_sGroupByColumn] + "'");
}
//returning grouped/counted result
return dtGroup;
}
Example:
例子：
DataTable desiredResult = GroupBy("TeamID", "MemberID", dt);
回答by esc
dt.AsEnumerable()
.GroupBy(r => new { Col1 = r["Col1"], Col2 = r["Col2"] })
.Select(g =>
{
var row = dt.NewRow();
row["PK"] = g.Min(r => r.Field<int>("PK"));
row["Col1"] = g.Key.Col1;
row["Col2"] = g.Key.Col2;
return row;
})
.CopyToDataTable();
