葡京网投哪个正规 > 首页 > all条件查询的用法

原标题:all条件查询的用法

浏览次数:97 时间:2020-05-08

Right Outer Join 运算符返回满足第二个输入与第一个输入的每个匹配行的联接的每行。此外,它还返回第二个输入中在第一个输入中没有匹配行的任何行,即与 NULL 联接。如果 Argument 列内不存在任何联接谓词,则每行都是一个匹配行。

all 语法

一、联接

scalar_expression { = | | != | | = | ! | | = | ! } ALL ( subquery )

通过联接,可以根据各个表之间的逻辑关系从两个或多个表中检索数据。联接表示应如何使用一个表中的数据来选择另一个表中的行。

scalar_expression 任何有效的表达式。

联接条件通过以下方法定义两个表在查询中的关联方式:

{ = | | != | | = | ! | | = | ! } 比较运算符。

指定每个表中要用于联接的列。典型的联接条件在一个表中指定外键,在另一个表中指定与其关联的键。指定比较各列的值时要使用的逻辑运算符。

subquery 返回单列结果集的子查询。返回列的数据类型必须与 scalar_expression 的数据类型相同。

下面的 SQL 语句说明 titles 表和 publishers 表之间的左向外联接包括所有的书名,甚至包括那些没有出版商信息的书名: Use pubs SELECT titles.title_id, titles.title, publishers.pub_name FROM titles LEFT OUTER JOIN publishers ON titles.pub_id = publishers.pub_id

受限的 SELECT 语句,其中不允许使用 ORDER BY 子句、COMPUTE 子句和 INTO 关键字。

实例

实例

1 create table employee(2 ID int,3 name nvarchar (10),4 salary int )5 GO12 create table job(3 ID int,4 title nvarchar (10),5 averageSalary int)6 GO123 insert into employee (ID, name, salary) values (1, 'Jason', 1234)4 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (2, 'Robert', 4321)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (3, 'Celia', 5432)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (4, 'Linda', 3456)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (5, 'David', 7654)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (6, 'James', 4567)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (7, 'Alison', 8744)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (8, 'Chris', 9875)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (9, 'Mary', 2345)2 GO

(1 rows affected)12 insert into job(ID, title, averageSalary) values(1,'Developer',3000)3 GO

(1 rows affected)1 insert into job(ID, title, averageSalary) values(2,'Tester', 4000)2 GO

(1 rows affected)1 insert into job(ID, title, averageSalary) values(3,'Designer', 5000)2 GO

(1 rows affected)1 insert into job(ID, title, averageSalary) values(4,'Programmer', 6000)2 GO

(1 rows affected)1234 select *葡京正网网投, from employee;5 GOID name salary----------- ---------- ----------- 1 Jason 1234 2 Robert 4321 3 Celia 5432 4 Linda 3456 5 David 7654 6 James 4567 7 Alison 8744 8 Chris 9875 9 Mary 2345

(9 rows affected)1 select * from job;2 GOID title averageSalary----------- ---------- ------------- 1 Developer 3000 2 Tester 4000 3 Designer 5000 4 Programmer 6000

(4 rows affected)12 -- RIGHT OUTER JOIN includes all the information from the table on the right.34 SELECT e.id, e.name, j.title5 FROM Employee e6 RIGHT OUTER JOIN job j7 ON e.id = j.id8 GOid name title----------- ---------- ---------- 1 Jason Developer 2 Robert Tester 3 Celia Designer 4 Linda Programmer

(4 rows affected)123 drop table employee;4 drop table job;5 GO12

以下示例创建一个存储过程,该过程确定是否能够在指定的天数中制造出 AdventureWorks2008R2 数据库教程中具有指定 SalesOrderID 的所有组件。该示例使用子查询为具有特定 SalesOrderID 的所有组件创建 DaysToManufacture 值的列表,然后确认所有 DaysToManufacture 都在指定的天数内。

实例二 RIGHT OUTER JOIN

USE AdventureWorks2008R2 ;GO

CREATE PROCEDURE DaysToBuild @OrderID int, @NumberOfDays intASIF @NumberOfDays = ALL ( SELECT DaysToManufacture FROM Sales.SalesOrderDetail JOIN Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID WHERE SalesOrderID = @OrderID )PRINT 'All items for this order can be manufactured in specified number of days or less.'ELSE PRINT 'Some items for this order cannot be manufactured in specified number of days or less.' ;

外连接语法

下面来看个完整的实例

左外连接的语法为: SELECT 列 FROM 表1 LEFT [OUTER]JOIN 表2 ON 表1.列1=表2.列2

1 create table employee(2 ID int,3 name nvarchar (10),4 salary int )5 GO12 create table job(3 ID int,4 title nvarchar (10),5 averageSalary int)6 GO123 insert into employee (ID, name, salary) values (1, 'Jason', 1234)4 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (2, 'Robert', 4321)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (3, 'Celia', 5432)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (4, 'Linda', 3456)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (5, 'David', 7654)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (6, 'James', 4567)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (7, 'Alison', 8744)2 GO

葡京网投哪个正规,(1 rows affected)1 insert into employee (ID, name, salary) values (8, 'Chris', 9875)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (9, 'Mary', 2345)2 GO

(1 rows affected)12 insert into job(ID, title, averageSalary) values(1,'Developer',3000)3 GO

(1 rows affected)1 insert into job(ID, title, averageSalary) values(2,'Tester', 4000)2 GO

(1 rows affected)1 insert into job(ID, title, averageSalary) values(3,'Designer', 5000)2 GO

(1 rows affected)1 insert into job(ID, title, averageSalary) values(4,'Programmer', 6000)2 GO

(1 rows affected)123 select * from employee;4 GOID name salary----------- ---------- ----------- 1 Jason 1234 2 Robert 4321 3 Celia 5432 4 Linda 3456 5 David 7654 6 James 4567 7 Alison 8744 8 Chris 9875 9 Mary 2345

(9 rows affected)1 select * from job;2 GOID title averageSalary----------- ---------- ------------- 1 Developer 3000 2 Tester 4000 3 Designer 5000 4 Programmer 6000

(4 rows affected)123 -- If your subquery returns a scalar value, you can use a comparison operator,45 SELECT e.ID,e.name6 FROM Employee e7 WHERE e.salary ALL (SELECT averageSalary FROM job j)8 GOID name----------- ---------- 5 David 7 Alison 8 Chris

(3 rows affected)123 drop table employee;4 drop table job;5 GO1

右外连接的语法为: SELECT select_list FROM 表1 RIGHT[OUTER]JOIN 表2 ON 表1.列1=表2.列2

全外连接(完整外部联接)的语法为: SELECT select_list FROM 表1 FULL[OUTER] JOIN 表2 ON 表1.列1=表2.列2

实例

1 create table employee(2 ID int,3 name nvarchar (10),4 salary int )5 GO12 create table job(3 ID int,4 title nvarchar (10),5 averageSalary int)6 GO123 insert into employee (ID, name, salary) values (1, 'Jason', 1234)4 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (2, 'Robert', 4321)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (3, 'Celia', 5432)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (4, 'Linda', 3456)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (5, 'David', 7654)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (6, 'James', 4567)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (7, 'Alison', 8744)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (8, 'Chris', 9875)2 GO

(1 rows affected)1 insert into employee (ID, name, salary) values (9, 'Mary', 2345)2 GO

(1 rows affected)12 insert into job(ID, title, averageSalary) values(1,'Developer',3000)3 GO

(1 rows affected)1 insert into job(ID, title, averageSalary) values(2,'Tester', 4000)2 GO

(1 rows affected)1 insert into job(ID, title, averageSalary) values(3,'Designer', 5000)2 GO

(1 rows affected)1 insert into job(ID, title, averageSalary) values(4,'Programmer', 6000)2 GO

(1 rows affected)123 select * from employee;4 GOID name salary----------- ---------- ----------- 1 Jason 1234 2 Robert 4321 3 Celia 5432 4 Linda 3456 5 David 7654 6 James 4567 7 Alison 8744 8 Chris 9875 9 Mary 2345

(9 rows affected)1 select * from job;2 GOID title averageSalary ---------- ------------- 1 Developer 3000 2 Tester 4000 3 Designer 5000 4 Programmer 6000

(4 rows affected)123 SELECT e.name FROM Employee e4 RIGHT OUTER JOIN job j5 ON e.id = j.id6 WHERE e.id IS NOT NULL7 GOname----------JasonRobertCeliaLinda

(4 rows affected)123 drop table employee;4 drop table job;5 GO1

本文由葡京网投哪个正规发布于首页,转载请注明出处:all条件查询的用法

关键词:

上一篇:没有了

下一篇:没有了