有朋友反饋說我提供的sample不能編譯。大概是版本的問題，可以到
http://msdn2.microsoft.com/en-us/bb330936.aspx
下載for beta1的版本。本節接著講groupby。
上一節，我們講了如何理解groupby返回的結果。本節會延這個思路闡述下去。先來看下面的例子
????????????
var
?q?
=
?
from
?p?
in
?db.Products
????????????????????
group
?p?
by
?p.CategoryID?
into
?g
????????????????????
select
?new?{?CategoryID?
=
?g.
Key
,?g?};
如果，使用下面的語句。
????????????
var
?q?
=
?
from
?p?
in
?db.Products
????????????????????
group
?p?
by
?p.CategoryID?
into
?g
????????????????????
select
?new?{?CategoryID?
=
?g.
Key
,GroupSet?
=
?g?};
????????????
foreach
?(var?gp?
in
?q)
????????????
{
????????????
????????????????
if
?(gp.CategoryID?
==
?
7
)
????????????????
{???
????????????????
????????????????????
foreach
?(var?p?
in
?gp.GroupSet)
????????????????????
{?
????????????????????
????????????????????}
????????????????}
????????????}
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
]
????
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
()
????????};
接著統計，同各類產品中，斷貨的產品數量。使用下面的語句。
????
var
?q?
=
????????
from
?p?
in
?db.Products
????????
group
?p?
by
?p.CategoryID?
into
?g
????????
select
?new?{
????????????g.
Key
,
????????????NumProducts?
=
?g.
Count
(p?
=>
?p.Discontinued)
????????};
????
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
()
????????};
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;
按產品單價是否大于１０分類。其結果為兩類，大于的是一類，小于及等于為另一類。好了，剩下的，大家自己多去領會。
?
TrackBack: http://www.cnblogs.com/126/archive/2007/07/10/812621.html
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
