sql select all条件查询的用法

all 语法

scalar_expression { = | | != | > | >= | !> |

scalar_expression
任何有效的表达式。

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

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

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

实例

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

复制

USE AdventureWorks2008R2 ;
GO

CREATE PROCEDURE DaysToBuild @OrderID int, @NumberOfDays int
AS
IF
@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.' ;

下面来看个完整的实例

1> create table employee(
2> ID int,
3> name nvarchar (10),
4> salary int )
5> GO
1>
2> create table job(
3> ID int,
4> title nvarchar (10),
5> averageSalary int)
6> GO
1>
2>
3> 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)
1>
2> 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)
1>
2>
3> * from employee;
4> GO
ID 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> GO
ID title averageSalary
----------- ---------- -------------
1 Developer 3000
2 Tester 4000
3 Designer 5000
4 Programmer 6000

(4 rows affected)
1>
2>
3> -- If your subquery returns a scalar value, you can use a comparison operator,
4>
5> SELECT e.ID,e.name
6> FROM Employee e
7> WHERE e.salary > ALL (SELECT averageSalary FROM job j)
8> GO
ID name
----------- ----------
5 David
7 Alison
8 Chris

(3 rows affected)
1>
2>
3> drop table employee;
4> drop table job;
5> GO
1>