sql select all条件查询的用法

all 语法

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

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> select * 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>