LINQ系列：LINQ to SQL Group by/Having分组
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]
