C#3.0入门系列（九）-之GroupBy操作

有朋友反馈说我提供的sample不能编译。大概是版本的问题，可以到http://msdn2.microsoft.com/en-us/bb330936.aspx下载for beta1的版本。本节接着讲groupby。
上一节，我们讲了如何理解groupby返回的结果。本节会延这个思路阐述下去。先来看下面的例子

GroupBy操作中Select的匿名类

var q = from p in db.Products

group p by p.CategoryID into g

select new { CategoryID = g.Key, g };

本例中，select操作中使用了匿名类。本系列中第一次提到匿名类是在http://www.cnblogs.com/126/archive/2006/12/20/503519.html 一文中。本文将再一次，阐述匿名类的理解。所谓匿名类，其实质，并不是匿名，而是编译器帮你去创建了这么一个类，在用户看来，好像并没有去创建，此所谓匿名类。也就是说，编译器在编译时，还是有这个类的，这个类是编译器自己创建的，其名称是编译器界定的。在上例的匿名类中，有2个property，一个叫CategoryID, 一个叫g。大家要注意了，这个匿名类，其实质是对返回结果重新进行了包装。而那个叫做g的property，就封装了一个完整的分组。如图，仔细比较和上篇图的区别。

如果，使用下面的语句。

var q = from p in db.Products

group p by p.CategoryID into g

select new { CategoryID = g.Key,GroupSet = g };

只是把g重新命名为GroupSet．需要用下面的遍历，获取每个产品纪录。

foreach (var gp in q)

{

if (gp.CategoryID == 7)

{

foreach (var p in gp.GroupSet)

{

}

这里groupby的操作相对难理解些，主要原因，它包含了整个分组的具体信息，而不是简单的求和，取平均值等。如果在最终结果中，也就是在select语句中，不包含g的全部信息，而只是g的聚合函数。又会是怎么样的一番风景呢？
GroupBy中的Max, Min, Sum, Average,Count
如果，只想取每类产品中，单价为最大，用T-sql该怎么办呢？是不是要这么来写

SELECT MAX([t0].[UnitPrice]) AS [MaxPrice], [t0].[CategoryID]

FROM [dbo].[Products] AS [t0]

GROUP BY [t0].[CategoryID]

我们来看看，dlinq如何来做同样的事情．如下，先按CategoryID归类，然后，只取CategoryID值和同类产品中单价最大的。

var q =

from p in db.Products

group p by p.CategoryID into g

select new {

g.Key,

MaxPrice = g.Max(p => p.UnitPrice)

};

在这里，Max函数只对每个分组进行操作。我们来看看其结果

呀，这次，dlinq并没有把组里所有的纪录都取出来的吗。（请参考http://www.cnblogs.com/126/archive/2006/09/01/486388.html一文中的方法，配置sample.） dlinq只是简单做了统计，并返回结果。
每类产品中，单价为最小的，

var q =

from p in db.Products

group p by p.CategoryID into g

select new {

g.Key,

MinPrice = g.Min(p => p.UnitPrice)

};

每类产品的价格平均值

var q =

from p in db.Products

group p by p.CategoryID into g

select new {

g.Key,

AveragePrice = g.Average(p => p.UnitPrice)

};

每类产品，价格之和

var q =

from p in db.Products

group p by p.CategoryID into g

select new {

g.Key,

TotalPrice = g.Sum(p => p.UnitPrice)

};

各类产品，数量之和

var q =

from p in db.Products

group p by p.CategoryID into g

select new {

g.Key,

NumProducts = g.Count()

};

如果用OrderDetails表做统计，会更好些，因为，不光可以统计同一种产品，还可以统计同一订单。
接着统计，同各类产品中，断货的产品数量。使用下面的语句。

var q =

from p in db.Products

group p by p.CategoryID into g

select new {

g.Key,

NumProducts = g.Count(p => p.Discontinued)

};

在这里，count函数里，使用了Lambda表达式。在上篇中，我们已经阐述了g是一个组的概念。那在该Lambda表达式中的p，就代表这个组里的一个元素或对象，即某一个产品。还可以使用where条件来限制最终筛选结果

var q =

from p in db.Products

group p by p.CategoryID into g

where g.Count() >= 10

select new {

g.Key,

ProductCount = g.Count()

};

这句在翻译成sql语句时，欠套了一层，在最外层加了条件。

SELECT [t1].[CategoryID], [t1].[value2] AS [ProductCount]

FROM (

SELECT COUNT(*) AS [value], COUNT(*) AS [value2], [t0].[CategoryID]

FROM [dbo].[Products] AS [t0]

GROUP BY [t0].[CategoryID]

) AS [t1]

WHERE [t1].[value] >= @p0

-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [10]

-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 2.0.20612.0

GroupBy操作中GroupBy的匿名类
在第一次谈到匿名类时，我们就提到不光Select操作可以使用匿名类，其他操作符也可以。但是，OrderBy不支持。请参考C#3.0入门系列（六）-之OrderBy操作

当用户既想按产品的分类，又想按供应商来做分组，该怎么办呢。这时，我们就该使用匿名类。

var categories =

from p in db.Products

group p by new { p.CategoryID, p.SupplierID } into g

select new {g.Key, g};

在by后面，new出来一个匿名类。这里，Key其实质是一个类的对象，Key包含两个Property,一个是CategoryID，再一个是SupplierID ，要想取到具体CategoryID的值，需要g.Key.CategoryID，才能访问到。我们来看dlinq翻译的T-sql语句。

SELECT [t0].[SupplierID], [t0].[CategoryID]

FROM [dbo].[Products] AS [t0]

GROUP BY [t0].[CategoryID], [t0].[SupplierID]

-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 2.0.20612.0

先按CategoryID，再按SupplierID ，和匿名类中的循序一样。
最后一个例子。

var categories =

from p in db.Products

group p by new { Criterion = p.UnitPrice > 10 } into g

select g;

按产品单价是否大于１０分类。其结果为两类，大于的是一类，小于及等于为另一类。好了，剩下的，大家自己多去领会。

posted on 2007-07-10 18:17 Tom Song 阅读(4124) 评论(16) 编辑收藏网摘所属分类: C# 3.0

Feedback

#1楼 [楼主] 2007-07-10 18:19 宋国安

突然感觉自己文章写的好差，被我一个师兄给打击了。灰心丧气中。
回复引用查看

#2楼 2007-07-10 20:15 一瓢

楼主是搞什么开发的，一个groupby的任务用dlinq写的这么复杂
回复引用查看

#3楼 2007-07-10 21:07 一瓢

好地方，呵呵
回复引用查看

#4楼 2007-07-29 03:57 木野狐

不错。
希望能从 designer 界面介绍实体之间关联的几种做法，1..1, 1..n, n..n
特别 n..n 的时候，如何做更好。
比如我 User 和 Group 两个实体，在数据库中通过 UserInGroup(UserId, GroupId) 这种方式关联，
dlinq 设计器默认下就是傻傻的做成 UserInGroup 也当成一个实体表吧？
有没有办法改变呢？
User.Groups....
Group.Users....

回复引用查看

#5楼 2007-08-16 22:43 XP主题[未注册用户]

试试去看！！！
回复引用

#6楼 2007-08-22 16:36 andy[未注册用户]

SELECT table.*
FROM table T1 INNER JOIN
(SELECT t1, MAX(t2) AS t2
FROM T1
GROUP BY t1) T2 ON T1.t1 = T2.t1 AND T1.t2 = T2.t2

请问这句sql 怎么用linq 实现
回复引用

#7楼 2007-08-23 10:34 andy[未注册用户]

具体的表
t1 t2 t3……n
--------------------------
aaa 100 剩余字段
bbb 80 剩余字段
aaa 90 剩余字段
ccc 70 剩余字段
ccc 500 剩余字段
ccc 20 剩余字段
bbb 30 剩余字段
bbb 40 剩余字段
bbb 50 剩余字段

我希望的最后结果就是：
t1 t2 t3……n
------------------
ccc 500 剩余字段
aaa 100 剩余字段
bbb 80 剩余字段

也就是以t1分组，取每组中t2最大的记录

回复引用

#8楼 2007-08-23 10:43 andy[未注册用户]

select new {c2.Column1, Column2 = Math.Max(c2.Column2)}

这句在vs2008里会提示 c2 不存在
必须改为
select new { Column1= g.Key, Column2 = g.Max(c2 =>c2.Column2) }
回复引用

#9楼 [楼主] 2007-08-23 12:33 宋国安

@andy
是的，是我写错了。。。坦白的讲，差点没有看懂你写了什么。。原来t是column而不是table。。。寒死了。。你贴个sql 脚本来吧。然后，insert 一些值。回头再帮你看哦。先去吃饭。
回复引用查看

#10楼 2007-08-23 18:01 Martin Yao[未注册用户]

非常麻烦,要回家了,明天给你更好的答案.
下面是一个方案.
var q = (from T1 in db.Customers
join T2 in
(db.Orders.GroupBy(p => p.CustomerID).Select(p => new Key { CustomerID = p.Key, City = p.Select(m => m.ShipCity).Max() }))
on new Key { City = T1.City, CustomerID = T1.CustomerID } equals new Key { City = T2.City, CustomerID = T2.CustomerID } into xxx
from xx in xxx
select xx).ToList();

public class Key
{
private string customerID;

public string CustomerID
{
get
{
return this.customerID;
}
set
{
this.customerID = value;
}
}

private string city;

public string City
{
get
{
return this.city;
}
set
{
this.city = value;
}
}
}
回复引用

#11楼 [楼主] 2007-08-23 20:27 宋国安

Martin, I think you misunderstanded andy's means.

Hi, Andy, would you provide sql script for related table? It will help us to resolve your issue.
回复引用查看

#12楼 2007-08-24 11:56 andy[未注册用户]

具体情况是现有表 pic 用来保存用户图片数据, 字段及内容如下，
<pre>
PicID Username PicFile PostDate
1 AA 070808.jpg 2007-08-08
2 BB 070809.jpg 2007-08-09
3 CC 078010.jpg 2007-08-10
4 AA 070811.jpg 2007-08-11
5 BB 070812.jpg 2007-08-12
6 CC 070813.jpg 2007-08-13
7 AA 070814.jpg 2007-08-14
8 BB 070815.jpg 2007-08-15
9 AA 070816.jpg 2007-08-16
10 BB 070817.jpg 2007-08-17
11 BB 070818.jpg 2007-08-18
12 CC 070819.jpg 2007-08-19
13 CC 070820.jpg 2007-08-20
14 CC 070821.jpg 2007-08-21
15 CC 070822.jpg 2007-08-22
</pre>

查询要求：找出每个用户最新发表的图片数据，也就是说三个用户只需输出每个用的最新图片数据，结果应如下
<pre>
PicID Username PicFile PostDate
9 AA 070816.jpg 2007-08-16
11 BB 070818.jpg 2007-08-18
15 CC 070822.jpg 2007-08-22
</pre>
sql 语句可正常执行
SELECT T1.*
FROM Pic T1 INNER JOIN
(SELECT Username, MAX(PostDate) AS MaxPostDate
FROM Pic
GROUP BY Username) T2 ON T1.Username = T2.Username AND T1.PostDate = T2.MaxPostDate
回复引用

#13楼 [楼主] 2007-08-24 15:37 宋国安

@andy
基本上知道你的意思，不过，不明白你的sql为什么写的这么复杂。很简单的语句就可以实现的。比如
SELECT MAX([t1].[UnitPrice]) AS [value], [t1].[CategoryID]
FROM [dbo].[Products] AS [t1]
GROUP BY [t1].[CategoryID]

它对应的 dlinq为
var q = (from c in db1.Products

group c by c.CategoryID into g

select new { CategoryID = g.Key, UnitPrice = g.Max(d => d.UnitPrice) }).ToList();

如果按你的思路，
SELECT [t2].[CategoryID], [t2].[value] AS [UnitPrice]
FROM [dbo].[Products] AS [t0]
CROSS JOIN (
SELECT MAX([t1].[UnitPrice]) AS [value], [t1].[CategoryID]
FROM [dbo].[Products] AS [t1]
GROUP BY [t1].[CategoryID]
) AS [t2]
WHERE ([t0].[CategoryID] = [t2].[CategoryID]) AND ([t0].[UnitPrice] = [t2].[value])

这个对应的dlinq 为
var q = (from c in db1.Products
join d in
(
from d in db1.Products
group d by d.CategoryID into g
select new { CategoryID = g.Key, UnitPrice = g.Max(e => e.UnitPrice) }

) on new { c.CategoryID, c.UnitPrice } equals new { d.CategoryID, d.UnitPrice }
into j
from jj in j
select jj).ToList();

其实，实现的是相同的东西。
回复引用查看

#14楼 2007-08-24 16:53 andy[未注册用户]

var q = (from c in db1.Products

group c by c.CategoryID into g

select new { CategoryID = g.Key, UnitPrice = g.Max(d => d.UnitPrice) }).ToList();

这种的局限就是，只能输出两个字段，实际要附带多个其他字段

var q = (from c in db1.Products
join d in
(
from d in db1.Products
group d by d.CategoryID into g
select new { CategoryID = g.Key, UnitPrice = g.Max(e => e.UnitPrice) }

) on new { c.CategoryID, c.UnitPrice } equals new { d.CategoryID, d.UnitPrice }
into j
from jj in j
select jj).ToList();

这种就可以输出和分组无关但却需要显示的其它数据，谢谢你的回复，这个已经决绝了我的需要

回复引用