子查询

最新推荐文章于 2023-11-07 16:26:23 发布

zhvsby

最新推荐文章于 2023-11-07 16:26:23 发布

阅读量407

点赞数

CC 4.0 BY-SA版权

分类专栏： .NET 4.0 文章标签： input string null sql c

本文链接：https://blog.youkuaiyun.com/zhvsby/article/details/6070628

.NET 4.0 专栏收录该内容

15 篇文章

订阅专栏

取相交项

描述：查询城市是A打头的顾客和城市包含A的顾客的交集，并按照顾客名字排序

查询句法：

var 取相交项 = (from c in ctx.Customers where c.City.Contains("A") select c).Intersect

(from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

对应SQL：

SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]

FROM (

SELECT DISTINCT [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 (EXISTS(

SELECT NULL AS [EMPTY]

FROM [dbo].[Customers] AS [t2]

WHERE ([t1].[CustomerID] = [t2].[CustomerID]) AND ([t2].[ContactName] LIKE @p0)

)) AND ([t1].[City] LIKE @p1)

ORDER BY [t1].[ContactName]

-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [A%]

-- @p1: Input String (Size = 3; Prec = 0; Scale = 0) [%A%]

排除相交项

描述：查询城市包含A的顾客并从中删除城市以A开头的顾客，并按照顾客名字排序

查询句法：

var 排除相交项 = (from c in ctx.Customers where c.City.Contains("A") select c).Except

(from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

对应SQL：

SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]

FROM (

SELECT DISTINCT [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 (NOT (EXISTS(

SELECT NULL AS [EMPTY]

FROM [dbo].[Customers] AS [t2]

WHERE ([t1].[CustomerID] = [t2].[CustomerID]) AND ([t2].[ContactName] LIKE @p0)

))) AND ([t1].[City] LIKE @p1)

ORDER BY [t1].[ContactName]

-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [A%]

-- @p1: Input String (Size = 3; Prec = 0; Scale = 0) [%A%]

子查询

描述：查询订单数超过5的顾客信息

查询句法：

var 子查询 = from c in ctx.Customers

where

(from o in ctx.Orders group o by o.CustomerID into o where o.Count() > 5 select o.Key).Contains(c.CustomerID)

select c;

对应SQL：

SELECT [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]

WHERE EXISTS(

SELECT NULL AS [EMPTY]

FROM (

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

FROM [dbo].[Orders] AS [t1]

GROUP BY [t1].[CustomerID]

) AS [t2]

WHERE ([t2].[CustomerID] = [t0].[CustomerID]) AND ([t2].[value] > @p0)

)

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