, . , . , , . , . ORDER BY , . . , Peel , 14.5.
SELECT a.snum, sname, onum, 'Highest on', odate
FROM Salespeople a, Orders b
WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt)
FROM Orders c
WHERE c.odate = b.odate)
UNION
SELECT a.snum, sname, onum, 'Lowest on', odate
FROM Salespeople a, Orders b
WHERE a.snum = b.snum AND b.amt = (SELECT MIN (amt)
FROM Orders c
WHERE c.odate = b.odate)
ORDER BY 3;
=============== SQL Execution Log ============
| (SELECT min (amt) |
| FROM Orders c |
| WHERE c.odate = b.odate) |
| ORDER BY 3; |
| ============================================= |
| |
| ----- ------- ------ ---------- ----------- |
| 1007 Rifkin 3001 Lowest on 10/03/1990 |
| 1002 Serres 3005 Highest on 10/03/1990 |
| 1002 Serres 3007 Lowest on 10/04/1990 |
| 1001 Peel 3008 Highest on 10/05/1990 |
| 1001 Peel 3008 Lowest on 10/05/1990 |
| 1003 Axelrod 3009 Highest on 10/04/1990 |
| 1002 Serres 3010 Lowest on 10/06/1990 |
| 1001 Peel 3011 Highest on 10/06/1990 |
===============================================
14.5. ORDER BY.
ORDER BY , . , ASC DESC , . , 3 ORDER BY , SELECT . , , , , . ( 7, ).
, , , . , , . .
, . , , , . , , , (NULL) snum. , sname .
|
|
, , , . , . , . , , , , , , , . , 14.6, :
SELECT Salespeople.snum, sname, cname, comm
FROM Salespeople, Customers
WHERE Salespeople.city = Customers.city
UNION
SELECT snum, sname, 'NO MATCH ', comm
FROM Salespeople
WHERE NOT city = ANY (SELECT city
FROM Customers)
ORDER BY 2 DESC;
=============== SQL Execution Log ============
| FROM Salespeople |
| WHERE NOT city = ANY (SELECT city |
| FROM Customers) |
| ORDER BY 2 DESC; |
| ============================================= |
| |
| ----- ------- --------- ------------ |
| 1002 Serres Cisneros 0.1300 |
| 1002 Serres Liu 0.1300 |
| 1007 Rifkin NO MATCH 0.1500 |
| 1001 Peel Clemens 0.1200 |
| 1001 Peel Hoffman 0.1200 |
| 1004 Motika Clemens 0.1100 |
| 1004 Motika Hoffman 0.1100 |
| 1003 Axelrod NO MATCH 0.1000 |
===============================================
14.6. .
'NO MATCH' , cname ( SQL). , . , . , , . UNION , . , , , :
SELECT a.snum, sname, a.city, 'MATCHED '
FROM Salespeople a, Customers b
WHERE a.city = b.city
UNION
SELECT snum, sname, city, 'NO MATCH'
FROM Salespeople
WHERE NOT city = ANY (SELECT city
FROM Customers)
ORDER BY 2 DESC;
14.7 .
=============== SQL Execution Log ============
| WHERE a.city = b.city |
| union |
| SELECT snum,sname,city, 'NO MATCH' |
| FROM Salespeople |
| WHERE NOT city = (SELECT city |
| FROM Customers) |
| ORDER BY 2 DESC; |
| ============================================= |
| |
| ----- ------- ------------ --------- |
| 1002 Serres San Jose MATCHED |
| 1007 Rifkin Barselona NO MATCH |
| 1001 Peel London MATCHED |
| 1004 Motika London MATCHED |
| 1003 Axelrod New York NO MATCH |
===============================================
14.7. .
, . , . , ( 14.8):
|
|
SELECT snum, city, 'SALESPERSON MATCH '
FROM Salespeople
WHERE NOT city = ANY (SELECT city
FROM Customers)
UNION
SELECT snum, city, 'SALESPERSON NO MATCH'
FROM Salespeople
WHERE NOT city = ANY (SELECT city
FROM Customers)
UNION
SELECT cnum, city, 'CUSTOMER MATCHED '
FROM Customers
WHERE city = ANY (SELECT city
FROM Salespeople)
UNION
SELECT cnum, city, 'CUSTOMER NO MATCH '
FROM Customers
WHERE NOT city = ANY (SELECT city
FROM Salespeople)
ORDER BY 2 DESC;
================= SQL Execution Log =============
| FROM Salespeople) |
| ORDER BY 2 DESC; |
| ================================================ |
| |
| ---- -------- ------------------------ |
| 2003 San Jose CUSTOMER MATCHED |
| 2008 San Jose CUSTOMER MATCHED |
| 2002 Rome CUSTOMER NO MATCH |
| 2007 Rome CUSTOMER NO MATCH |
| 1003 New York SALESPERSON MATCHED |
| 1003 New York SALESPERSON NO MATCH |
| 2001 London CUSTOMER MATCHED |
| 2006 London CUSTOMER MATCHED |
| 2004 Berlin CUSTOMER NO MATCH |
| 1007 Barcelona SALESPERSON MATCHED |
| 1007 Barcelona SALESPERSON NO MATCH |
==================================================
14.8. .
, , ANY, .
, , , . , , . , , , . ,
query X UNION query Y UNION query Z;
,
(query X UNION query Y)UNION query Z;
query X UNION (query Y UNION query Z);
, UNION UNION ALL , , .
(query X UNION ALL query Y)UNION query Z;
query X UNION ALL(query Y UNION query Z);
.
, UNION, . (, ), , , . , , , , , .
. SQL. , . , , , .
SQL
1. , , , . , rating=200 , , , " ", " ".
2. , , . .
3. . snum San Jose; , cnum San Jose; onum 3 . , . : . .
(. A .)