Linq to sql ：查询句法---分页分组查询

描述：按照每页10 条记录，查询第二页的顾客

 

查询句法：

 

var  分页 = (from c inctx.Customers select c).Skip(10).Take(10);

 

对应SQL：

 

SELECT TOP 10 [t1].[CustomerID],[t1].[CompanyName], [t1].[ContactName],

 

[t1].[ContactTitle], [t1].[Address],[t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country],

 

[t1].[Phone], [t1].[Fax]

 

FROM (

 

   SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CustomerID],

 

[t0].[CompanyName], [t0].[ContactName],[t0].[ContactTitle], [t0].[Address], [t0].[City],

 

[t0].[Region], [t0].[PostalCode],[t0].[Country], [t0].[Phone], [t0].[Fax]) AS

 

[ROW_NUMBER], [t0].[CustomerID], [t0].[CompanyName],[t0].[ContactName],

 

[t0].[ContactTitle], [t0].[Address],[t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country],

 

[t0].[Phone], [t0].[Fax]

 

   FROM [dbo].[Customers] AS [t0]

 

       ) AS [t1]

 

WHERE [t1].[ROW_NUMBER] > @p0

 

-- @p0: Input Int32 (Size = 0; Prec = 0;Scale = 0) [10]

分组

 

描述：根据顾客的国家分组，查询顾客数大于5 的国家名和顾客数

 

查询句法：

 

var  一般分组 = from c inctx.Customers

 

group c by c.Country into g

 

where g.Count() > 5

 

orderby g.Count() descending

 

select new

 

                                      {

 

国家 =g.Key,

 

顾客数 =g.Count()

 

                                      };

 

对应SQL：

 

SELECT [t1].[Country], [t1].[value3] AS [顾客数]

 

FROM (

 

       SELECT COUNT(*) AS [value], COUNT(*) AS [value2], COUNT(*) AS [value3],

 

[t0].[Country]

 

   FROM [dbo].[Customers] AS [t0]

 

   GROUP BY [t0].[Country]

 

       ) AS [t1]

 

WHERE [t1].[value] > @p0

 

ORDER BY [t1].[value2] DESC

 

-- @p0: Input Int32 (Size = 0; Prec = 0;Scale = 0) [5]

 

 

 

描述：根据国家和城市分组，查询顾客覆盖的国家和城市

 

查询句法：

 

var  匿名类型分组 = from c inctx.Customers

 

group c by new { c.City, c.Country } into g

 

orderby g.Key.Country, g.Key.City

 

select new

 

                                          {

 

国家 =g.Key.Country,

 

城市 =g.Key.City

 

                                          };

 

对应SQL：

 

SELECT [t1].[Country], [t1].[City]

 

FROM (

 

   SELECT [t0].[City], [t0].[Country]

 

   FROM [dbo].[Customers] AS [t0]

 

   GROUP BY [t0].[City], [t0].[Country]

 

       ) AS [t1]

 

ORDER BY [t1].[Country], [t1].[City]

 

 

 

描述：按照是否超重条件分组，分别查询订单数量

 

查询句法：

 

var  按照条件分组 = from o inctx.Orders

 

group o by new {  条件  = o.Freight >100 } into g

 

select new

 

                                          {

 

数量 =g.Count(),

 

是否超重 =g.Key.条件 ? "是" : "否"

 

                                          };

 

对应SQL：

 

SELECT 

 

       (CASE  

 

       WHEN [t2].[value2] = 1 THEN @p1

 

                ELSE @p2

 

         END) AS [value], [t2].[value] AS [数量]

 

FROM (

 

       SELECT COUNT(*) AS [value], [t1].[value] AS [value2]

 

       FROM (

 

                SELECT  

 

           (CASE 

 

                WHEN [t0].[Freight] > @p0THEN 1

 

                WHEN NOT ([t0].[Freight] >@p0) THEN 0

 

                                ELSE NULL

 

                          END) AS [value]

 

       FROM [dbo].[Orders] AS [t0]

 

       ) AS [t1]

 

   GROUP BY [t1].[value]

 

       ) AS [t2]

 

-- @p0: Input Currency (Size = 0; Prec =19; Scale = 4) [100]

 

-- @p1: Input String (Size = 1; Prec = 0;Scale = 0) [是]

 

-- @p2: Input String (Size = 1; Prec = 0;Scale = 0) [否]