注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

午夜阳光

 
 
 

日志

 
 

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

2015-01-15 11:44:02|  分类: Linq |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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 CategoryId from Products group by CategoryId
   
复制代码

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 
        [Extent1].[CategoryID] AS [K1], 
        MAX([Extent1].[UnitPrice]) AS [A1]
        FROM [dbo].[Product] AS [Extent1]
        GROUP BY [Extent1].[CategoryID]
   
复制代码

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 
        [Extent1].[CategoryID] AS [K1], 
        MIN([Extent1].[UnitPrice]) AS [A1]
        FROM [dbo].[Product] AS [Extent1]
        GROUP BY [Extent1].[CategoryID]
   
复制代码

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 
        [Extent1].[CategoryID] AS [K1], 
        AVG([Extent1].[UnitPrice]) AS [A1]
        FROM [dbo].[Product] AS [Extent1]
        GROUP BY [Extent1].[CategoryID]
   
复制代码

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 
        [Extent1].[CategoryID] AS [K1], 
        SUM([Extent1].[UnitPrice]) AS [A1]
        FROM [dbo].[Product] AS [Extent1]
        GROUP BY [Extent1].[CategoryID]
   
复制代码

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]

复制代码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]
复制代码
  评论这张
 
阅读(1307)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017