一步一步学Linq to sql(6)

订单号 = o.OrderID, 是否超重 = o.Freight > 100 ? \是\ : \否\ }; 对应SQL： SELECT [t0].[OrderID], (CASE WHEN [t0].[Freight] > @p0 THEN @p1 ELSE @p2 END) AS [value] FROM [dbo].[Orders] AS [t0] -- @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) [否] where 描述：查询顾客的国家、城市和订单数信息，要求国家是法国并且订单数大于5 查询句法： var 多条件 = from c in ctx.Customers where c.Country == \ && c.Orders.Count > 5 select new { 国家 = c.Country, 城市 = c.City, 订单数 = c.Orders.Count }; 对应SQL： SELECT [t0].[Country], [t0].[City], ( SELECT COUNT(*) FROM [dbo].[Orders] AS [t2] WHERE [t2].[CustomerID] = [t0].[CustomerID] ) AS [value] FROM [dbo].[Customers] AS [t0] WHERE ([t0].[Country] = @p0) AND ((( SELECT COUNT(*) FROM [dbo].[Orders] AS [t1] WHERE [t1].[CustomerID] = [t0].[CustomerID] )) > @p1) -- @p0: Input String (Size = 6; Prec = 0; Scale = 0) [France] -- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [5] orderby 描述：查询所有没有下属雇员的雇用年和名，按照雇用年倒序，按照名正序 查询句法： var 排序 = from emp in ctx.Employees where emp.Employees.Count == 0 orderby emp.HireDate.Value.Year descending, emp.FirstName ascending select new { 雇用年 = emp.HireDate.Value.Year, 名 = emp.FirstName }; 对应SQL： SELECT DATEPART(Year, [t0].[HireDate]) AS [value], [t0].[FirstName] FROM [dbo].[Employees] AS [t0] WHERE (( SELECT COUNT(*) FROM [dbo].[Employees] AS [t1] WHERE [t1].[ReportsTo] = [t0].[EmployeeID] )) = @p0 ORDER BY DATEPART(Year, [t0].[HireDate]) DESC, [t0].[FirstName] -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [0] 分页 描述：按照每页10条记录，查询第二页的顾客 查询句法： var 分页 = (from c in ctx.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 in ctx.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 in ctx.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 in ctx.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] > @p0 THEN 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) [否] distinct 描述：查询顾客覆盖的国家 查询句法： var 过滤相同项 = (from c in ctx.Customers orderby c.Country select c.Country).Distinct(); 对应SQL： SELECT DISTINCT [t0].[Country] FROM [dbo].[Customers] AS [t0] union 描述：查询城市是A打头和城市包含A的顾客并按照顾客名字排序 …… 此处隐藏：2317字，全部文档内容请下载后查看。喜欢就下载吧 ……