LINQ系列:LINQ to SQL Group by/Having分组

时间:2022-01-30 03:00:01

1. 简单形式

var expr = from p in context.Products
group p by p.CategoryID into g
select g;

foreach (var item in expr)
{
Console.WriteLine(item.Key);

foreach (var p in item)
{
Console.WriteLine(
"{0}-{1}", p.ProductID, p.ProductName);
}
}
SELECT 
[Project2].[CategoryID] AS [CategoryID],
[Project2].[C1] AS [C1],
[Project2].[ProductID] AS [ProductID],
[Project2].[CategoryID1] AS [CategoryID1],
[Project2].[ProductName] AS [ProductName],
[Project2].[UnitPrice] AS [UnitPrice],
[Project2].[UnitsInStock] AS [UnitsInStock],
[Project2].[Discontinued] AS [Discontinued]
FROM ( SELECT
[Distinct1].[CategoryID] AS [CategoryID],
[Extent2].[ProductID] AS [ProductID],
[Extent2].[CategoryID] AS [CategoryID1],
[Extent2].[ProductName] AS [ProductName],
[Extent2].[UnitPrice] AS [UnitPrice],
[Extent2].[UnitsInStock] AS [UnitsInStock],
[Extent2].[Discontinued] AS [Discontinued],
CASE WHEN ([Extent2].[ProductID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM (SELECT DISTINCT
[Extent1].[CategoryID] AS [CategoryID]
FROM [dbo].[Product] AS [Extent1] ) AS [Distinct1]
LEFT OUTER JOIN [dbo].[Product] AS [Extent2] ON [Distinct1].[CategoryID] = [Extent2].[CategoryID]
)
AS [Project2]
ORDER BY [Project2].[CategoryID] ASC, [Project2].[C1] ASC

2. 最大值

var expr = from p in context.Products
group p by p.CategoryID into g
select new
{
g.Key,
MaxUnitPrice
= g.Max(p => p.UnitPrice)
};

foreach (var item in expr)
{
Console.WriteLine(
"{0}-{1}", item.Key, item.MaxUnitPrice);
}
SELECT 
[GroupBy1].[K1] AS [CategoryID],
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
[Extent1].[CategoryID] AS [K1],
MAX([Extent1].[UnitPrice]) AS [A1]
FROM [dbo].[Product] AS [Extent1]
GROUP BY [Extent1].[CategoryID]
)
AS [GroupBy1]

3. 最小值

var expr = from p in context.Products
group p by p.CategoryID into g
select new
{
g.Key,
MinUnitPrice
= g.Min(p => p.UnitPrice)
};
SELECT 
[GroupBy1].[K1] AS [CategoryID],
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
[Extent1].[CategoryID] AS [K1],
MIN([Extent1].[UnitPrice]) AS [A1]
FROM [dbo].[Product] AS [Extent1]
GROUP BY [Extent1].[CategoryID]
)
AS [GroupBy1]

4. 平均值

var expr = from p in context.Products
group p by p.CategoryID into g
select new
{
g.Key,
AverageUnitPrice
= g.Average(p => p.UnitPrice)
};
SELECT 
[GroupBy1].[K1] AS [CategoryID],
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
[Extent1].[CategoryID] AS [K1],
AVG([Extent1].[UnitPrice]) AS [A1]
FROM [dbo].[Product] AS [Extent1]
GROUP BY [Extent1].[CategoryID]
)
AS [GroupBy1]

5. 求和

var expr = from p in context.Products
group p by p.CategoryID into g
select new
{
g.Key,
TotalUnitPrice
= g.Sum(p => p.UnitPrice)
};
SELECT 
[GroupBy1].[K1] AS [CategoryID],
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
[Extent1].[CategoryID] AS [K1],
SUM([Extent1].[UnitPrice]) AS [A1]
FROM [dbo].[Product] AS [Extent1]
GROUP BY [Extent1].[CategoryID]
)
AS [GroupBy1]

6. 计数

var expr = from g in
from p in context.Products
group p by p.CategoryID
select new
{
CategoryID
= g.Key,
ProductsNumber
= g.Count()
};
var expr = from p in context.Products
group p by p.CategoryID into g
select new
{
g.Key,
ProductNumber
= g.Count()
};
var expr = context.Products
.GroupBy(p
=> p.CategoryID)
.Select(g
=> new
{
CategoryID
= g.Key,
ProductNumber
= g.Count()
});
SELECT 
[GroupBy1].[K1] AS [CategoryID],
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
[Extent1].[CategoryID] AS [K1],
COUNT(1) AS [A1]
FROM [dbo].[Product] AS [Extent1]
GROUP BY [Extent1].[CategoryID]
)
AS [GroupBy1]
var expr = from p in context.Products
group p by p.CategoryID into g
select new
{
g.Key,
ProductNumber
= g.Count(p => p.UnitsInStock > 0)
};

7. Where限制

var expr = from p in context.Products
group p by p.CategoryID into g
where g.Count() > 10
select new
{
g.Key,
ProductNumber
= g.Count()
};
SELECT 
[GroupBy1].[K1] AS [CategoryID],
[GroupBy1].[A2] AS [C1]
FROM ( SELECT
[Extent1].[CategoryID] AS [K1],
COUNT(1) AS [A1],
COUNT(1) AS [A2]
FROM [dbo].[Product] AS [Extent1]
GROUP BY [Extent1].[CategoryID]
)
AS [GroupBy1]
WHERE [GroupBy1].[A1] > 10

8. 多列分组

var expr = from p in context.Products
group p by
new
{
p.CategoryID,
p.Discontinued
}
into g
select new
{
g.Key,
ProductNumber
= g.Count()
};
SELECT 
[GroupBy1].[K1] AS [CategoryID],
[GroupBy1].[K3] AS [C1],
[GroupBy1].[K2] AS [Discontinued],
[GroupBy1].[A1] AS [C2]
FROM ( SELECT
[Extent1].[K1] AS [K1],
[Extent1].[K2] AS [K2],
[Extent1].[K3] AS [K3],
COUNT([Extent1].[A1]) AS [A1]
FROM ( SELECT
[Extent1].[CategoryID] AS [K1],
[Extent1].[Discontinued] AS [K2],
1 AS [K3],
1 AS [A1]
FROM [dbo].[Product] AS [Extent1]
)
AS [Extent1]
GROUP BY [K1], [K2], [K3]
)
AS [GroupBy1]
var expr = from p in context.Products
group p by
new
{
p.CategoryID,
p.Discontinued
}
into g
select new
{
g.Key.CategoryID,
ProductNumber
= g.Count()
};
SELECT 
[GroupBy1].[K1] AS [CategoryID],
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
[Extent1].[CategoryID] AS [K1],
[Extent1].[Discontinued] AS [K2],
COUNT(1) AS [A1]
FROM [dbo].[Product] AS [Extent1]
GROUP BY [Extent1].[CategoryID], [Extent1].[Discontinued]
)
AS [GroupBy1]

9. 表达式

var expr = from p in context.Products
group p by
new
{
Criteria
= p.UnitPrice > 10m
}
into g
select new
{
g.Key,
ProductNumber
= g.Count()
};

语句描述 :使用Group By返回两个产品序列。第一个序列包含单价大于10的产品。第二个序列包含单价小于或等于10的产品。

说明:按产品单价是否大于10分类 。其结果分为两类,大于的是一类,小于或等于是另一类。

SELECT 
[GroupBy1].[K1] AS [C1],
[GroupBy1].[K2] AS [C2],
[GroupBy1].[A1] AS [C3]
FROM ( SELECT
[Extent1].[K1] AS [K1],
[Extent1].[K2] AS [K2],
COUNT([Extent1].[A1]) AS [A1]
FROM ( SELECT
1 AS [K1],
CASE WHEN ([Extent1].[UnitPrice] > cast(10 as decimal(18))) THEN cast(1 as bit) WHEN ( NOT ([Extent1].[UnitPrice] > cast(10 as decimal(18)))) THEN cast(0 as bit) END AS [K2],
1 AS [A1]
FROM [dbo].[Product] AS [Extent1]
)
AS [Extent1]
GROUP BY [K1], [K2]
)
AS [GroupBy1]

10. 多表连接查询

var expr = from d in context.OrderDetails
join o
in context.Orders on d.OrderID equals o.OrderID
join p
in context.Products on d.ProductID equals p.ProductID
select new
{
o.OrderID,
o.UserID,
p.ProductID,
p.ProductName,
d.Quantity
};
SELECT 
[Extent2].[OrderID] AS [OrderID],
[Extent2].[UserID] AS [UserID],
[Extent3].[ProductID] AS [ProductID],
[Extent3].[ProductName] AS [ProductName],
[Extent1].[Quantity] AS [Quantity]
FROM [dbo].[OrderDetail] AS [Extent1]
INNER JOIN [dbo].[Order] AS [Extent2] ON [Extent1].[OrderID] = [Extent2].[OrderID]
INNER JOIN [dbo].[Product] AS [Extent3] ON [Extent1].[ProductID] = [Extent3].[ProductID]