• 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]
  • 相关阅读:
    LibreOJ2095
    Codeforces
    LibreOJ2241
    LibreOJ2044
    LibreOJ2043
    LibreOJ2045
    LibreOJ2042
    LibreOJ2097
    洛谷P4175
    POJ2888
  • 原文地址:https://www.cnblogs.com/libingql/p/4049842.html
Copyright © 2020-2023  润新知