select
描述:查询顾客的公司名、地址信息
查询句法:
var |
对应SQL:
SELECT [t0].[CompanyName],[t0].[Address] FROM [dbo].[Customers] AS[t0] |
描述:查询职员的姓名和雇用年份
查询句法:
var |
对应SQL:
SELECT [t0].[LastName] + [t0].[FirstName] AS[value], DATEPART(Year, [t0].[HireDate]) AS [value2] FROM [dbo].[Employees] AS [t0] |
描述:查询顾客的ID以及联系信息(职位和联系人)
查询句法:
var |
对应SQL:
SELECT [t0].[CustomerID], [t0].[ContactTitle],[t0].[ContactName] FROM [dbo].[Customers] AS [t0] |
描述:查询订单号和订单是否超重的信息
查询句法:
var |
对应SQL:
SELECT [t0].[OrderID], 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 |
对应SQL:
SELECT [t0].[Country], [t0].[City], ( FROM [dbo].[Customers] AS [t0] WHERE ([t0].[Country] = @p0) AND ((( -- @p0: Input String (Size = 6; Prec = 0; Scale =0) [France] -- @p1: Input Int32 (Size = 0; Prec = 0; Scale =0)[5] |
orderby
描述:查询所有没有下属雇员的雇用年和名,按照雇用年倒序,按照名正序
查询句法:
|
对应SQL:
SELECT DATEPART(Year, [t0].[HireDate]) AS [value],[t0].[FirstName] FROM [dbo].[Employees] AS [t0] WHERE (( ORDER BY DATEPART(Year, [t0].[HireDate]) DESC,[t0].[FirstName] -- @p0: Input Int32 (Size = 0; Prec = 0; Scale =0) [0] |
分页
描述:按照每页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 ( WHERE [t1].[ROW_NUMBER] >@p0 -- @p0: Input Int32 (Size = 0; Prec = 0; Scale =0) [10] |
分组
描述:根据顾客的国家分组,查询顾客数大于5的国家名和顾客数
查询句法:
|
对应SQL:
SELECT [t1].[Country], [t1].[value3] AS[顾客数] FROM ( WHERE [t1].[value] > @p0 ORDER BY [t1].[value2] DESC -- @p0: Input Int32 (Size = 0; Prec = 0; Scale =0) [5] |
描述:根据国家和城市分组,查询顾客覆盖的国家和城市
查询句法:
|
对应SQL:
SELECT [t1].[Country], [t1].[City] FROM ( ORDER BY [t1].[Country], [t1].[City] |
描述:按照是否超重条件分组,分别查询订单数量
查询句法:
var |
对应SQL:
SELECT FROM ( -- @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 |
对应SQL:
SELECT DISTINCT [t0].[Country] FROM [dbo].[Customers] AS [t0] |
union
描述:查询城市是A打头和城市包含A的顾客并按照顾客名字排序
查询句法:
var |
对应SQL:
SELECT [t3].[CustomerID], [t3].[CompanyName],[t3].[ContactName], [t3].[ContactTitle], [t3].[Address],[t3].[City], [t3].[Region], [t3].[PostalCode], [t3].[Country],[t3].[Phone], [t3].[Fax] FROM ( ORDER BY [t3].[ContactName] -- @p0: Input String (Size = 3; Prec = 0; Scale =0) [%A%] -- @p1: Input String (Size = 2; Prec = 0; Scale =0) [A%] |
concat
描述:查询城市是A打头和城市包含A的顾客并按照顾客名字排序,相同的顾客信息不会过滤
查询句法:
var |
对应SQL:
SELECT [t3].[CustomerID], [t3].[CompanyName],[t3].[ContactName], [t3].[ContactTitle], [t3].[Address],[t3].[City], [t3].[Region], [t3].[PostalCode], [t3].[Country],[t3].[Phone], [t3].[Fax] FROM ( ORDER BY [t3].[ContactName] -- @p0: Input String (Size = 3; Prec = 0; Scale =0) [%A%] -- @p1: Input String (Size = 2; Prec = 0; Scale =0) [A%] |
取相交项
描述:查询城市是A打头的顾客和城市包含A的顾客的交集,并按照顾客名字排序
查询句法:
var |
对应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 ( WHERE (EXISTS( 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 |
对应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 ( WHERE (NOT (EXISTS( 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 |
对应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( -- @p0: Input Int32 (Size = 0; Prec = 0; Scale =0) [5] |
in操作
描述:查询指定城市中的客户
查询句法:
|
对应SQL:
FROM [dbo].[Customers] AS [t0] WHERE [t0].[City] IN (@p0, @p1, @p2) -- @p0: Input String (Size = 11; Prec = 0; Scale =0) [Brandenburg] -- @p1: Input String (Size = 5; Prec = 0; Scale =0) [Cowes] -- @p2: Input String (Size = 7; Prec = 0; Scale =0) [Stavern] |
join
描述:内连接,没有分类的产品查询不到
查询句法:
var |
对应SQL:
SELECT COUNT(*) AS [value] FROM [dbo].[Products] AS [t0] INNER JOIN [dbo].[Categories] AS [t1] ON[t0].[CategoryID] = ([t1].[CategoryID]) |
描述:外连接,没有分类的产品也能查询到
查询句法:
var |
对应SQL:
SELECT COUNT(*) AS [value] FROM [dbo].[Products] AS [t0] LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON[t0].[CategoryID] = ([t1].[CategoryID]) |
本文转自:http://www.cnblogs.com/lovecherry/archive/2007/08/17/859826.html
相关内容网址:http://www.cnblogs.com/lovecherry/archive/2007/08/13/853754.html