SQL Server/T-SQL/Subquery/Subqueries ANY

Материал из SQL эксперт
Перейти к: навигация, поиск

ANY evaluates to true if the result of an inner query contains at least one row that satisfies the comparison

   <source lang="sql">

1> 2> CREATE TABLE works_on (emp_no INTEGER NOT NULL, 3> project_no CHAR(4) NOT NULL, 4> job CHAR (15) NULL, 5> enter_date DATETIME NULL) 6> 7> insert into works_on values (1, "p1", "analyst", "1997.10.1") 8> insert into works_on values (1, "p3", "manager", "1999.1.1") 9> insert into works_on values (2, "p2", "clerk", "1998.2.15") 10> insert into works_on values (2, "p2", NULL, "1998.6.1") 11> insert into works_on values (3, "p2", NULL, "1997.12.15") 12> insert into works_on values (4, "p3", "analyst", "1998.10.15") 13> insert into works_on values (5, "p1", "manager", "1998.4.15") 14> insert into works_on values (6, "p1", NULL, "1998.8.1") 15> insert into works_on values (7, "p2", "clerk", "1999.2.1") 16> insert into works_on values (8, "p3", "clerk", "1997.11.15") 17> insert into works_on values (7, "p1", "clerk", "1998.1.4") 18> GO Msg 2714, Level 16, State 6, Server sqle\SQLEXPRESS, Line 2 There is already an object named "works_on" in the database. 1> 2> 3> 4> -- ANY evaluates to true if the result of an inner query contains at least one row that satisfies the comparison 5> 6> SELECT DISTINCT emp_no, project_no, job FROM works_on WHERE enter_date > ANY 7> (SELECT enter_date FROM works_on) 8> GO emp_no project_no job


---------- ---------------
         1 p3         manager
         2 p2         NULL
         2 p2         clerk
         3 p2         NULL
         4 p3         analyst
         5 p1         manager
         6 p1         NULL
         7 p1         clerk
         7 p2         clerk
         8 p3         clerk

(10 rows affected) 1> 2> drop table works_on 3> GO 1> 2>

      </source>
   
  


Subqueries and ANY operator

   <source lang="sql">

29> 30> CREATE TABLE employee (emp_no INTEGER NOT NULL, 31> emp_fname CHAR(20) NOT NULL, 32> emp_lname CHAR(20) NOT NULL, 33> dept_no CHAR(4) NULL) 34> 35> insert into employee values(1, "Matthew", "Smith", "d3") 36> insert into employee values(2, "Ann", "Jones", "d3") 37> insert into employee values(3, "John", "Barrimore","d1") 38> insert into employee values(4, "James", "James", "d2") 39> insert into employee values(5, "Elsa", "Bertoni", "d2") 40> insert into employee values(6, "Elke", "Hansel", "d2") 41> insert into employee values(7, "Sybill", "Moser", "d1") 42> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> select * from employee 2> GO emp_no emp_fname emp_lname dept_no


-------------------- -------------------- -------
         1 Matthew              Smith                d3
         2 Ann                  Jones                d3
         3 John                 Barrimore            d1
         4 James                James                d2
         5 Elsa                 Bertoni              d2
         6 Elke                 Hansel               d2
         7 Sybill               Moser                d1

(7 rows affected) 1> 2> CREATE TABLE works_on (emp_no INTEGER NOT NULL, 3> project_no CHAR(4) NOT NULL, 4> job CHAR (15) NULL, 5> enter_date DATETIME NULL) 6> 7> insert into works_on values (1, "p1", "analyst", "1997.10.1") 8> insert into works_on values (1, "p3", "manager", "1999.1.1") 9> insert into works_on values (2, "p2", "clerk", "1998.2.15") 10> insert into works_on values (2, "p2", NULL, "1998.6.1") 11> insert into works_on values (3, "p2", NULL, "1997.12.15") 12> insert into works_on values (4, "p3", "analyst", "1998.10.15") 13> insert into works_on values (5, "p1", "manager", "1998.4.15") 14> insert into works_on values (6, "p1", NULL, "1998.8.1") 15> insert into works_on values (7, "p2", "clerk", "1999.2.1") 16> insert into works_on values (8, "p3", "clerk", "1997.11.15") 17> insert into works_on values (7, "p1", "clerk", "1998.1.4") 18> GO Msg 2714, Level 16, State 6, Server sqle\SQLEXPRESS, Line 2 There is already an object named "works_on" in the database. 1> 2> select * from works_on 3> GO emp_no project_no job enter_date


---------- --------------- -----------------------
         1 p1         analyst         1997-10-01 00:00:00.000
         1 p3         manager         1999-01-01 00:00:00.000
         2 p2         clerk           1998-02-15 00:00:00.000
         2 p2         NULL            1998-06-01 00:00:00.000
         3 p2         NULL            1997-12-15 00:00:00.000
         4 p3         analyst         1998-10-15 00:00:00.000
         5 p1         manager         1998-04-15 00:00:00.000
         6 p1         NULL            1998-08-01 00:00:00.000
         7 p2         clerk           1999-02-01 00:00:00.000
         8 p3         clerk           1997-11-15 00:00:00.000
         7 p1         clerk           1998-01-04 00:00:00.000

(11 rows affected) 1> 2> SELECT emp_fname, emp_lname FROM employee WHERE emp_no = ANY 3> (SELECT emp_no FROM works_on WHERE project_no = "p1") 4> GO emp_fname emp_lname


--------------------

Matthew Smith Elsa Bertoni Elke Hansel Sybill Moser (4 rows affected) 1> 2> drop table employee 3> GO 1> 2>

      </source>