UNION . SQL, DISTINCT . , 20,
SELECT snum, city
FROM Customers;
snum | city |
London | |
Rome | |
San Jose | |
Berlin | |
London | |
Rome | |
San Jose |
20
(snum=1001, city=London), , SQL . , UNION , . 21 .
SELECT snum, city
FROM Customers
UNION
SELECT snum, city
FROM Salespeople;
London | |
Berlin | |
San Jose | |
New York | |
Rome | |
London | |
Rome | |
Barcelona | |
San Jose |
21 UNION
( SQL, UNION ALL UNION, :
SELECT snum, city
FROM Customers
UNION ALL
SELECT snum, city
FROM Salespeople;
union
, SELECT UNION. ANSI, . , . , , . , . , .
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);
22.
'Lowest on', 'Highest on'. Peel ( ) 5 . , .
Peel | Highest on | 10/05/1990 | ||
Peel | Lowest on | 10/05/1990 | ||
Peel | Highest on | 10/06/1990 | ||
Serres | Highest on | 10/03/1990 | ||
Serres | Lowest on | 10/04/1990 | ||
Serres | Lowest on | 10/06/1990 | ||
Axelrod | Highest on | 10/04/1990 | ||
Rifkin | Lowest on | 10/03/1990 |
22 ,
|
|
union order by
, . . , . . , ORDER BY , . . , Peel , 23.
SELECT af.snum, sname, onum, 'Highest on', odate
FROM Salespeople af, Orders bf
WHERE af.snum=bf.snum
AND bf.amt=(SELECT MAX (amt)
FROM Orders cf
WHERE cf.odate=bf.odate)
UNION
SELECT af.snum, sname, onum, 'Lowest on', odate
FROM Salespeople af, Orders bf
WHERE af.snum=bf.snum
AND bf.amt=(SELECT MIN (amt)
FROM Orders cf
WHERE cf.odate=bf.odate)
ORDER BY 3
Rifkin | Lowest on | 10/03/1990 | ||
Serres | Highest on | 10/03/1990 | ||
Serres | Lowest on | 10/04/1990 | ||
Peel | Highest on | 10/05/1990 | ||
Peel | Lowest on | 10/05/1990 | ||
Axelrod | Highest on | 10/04/1990 | ||
Serres | Lowest on | 10/06/1990 | ||
Peel | Highest on | 10/06/1990 |
23 ORDER BY
ORDER BY , . , ASC DESC , . , 3 ORDER BY SELECT . - , , , . . ( 7 .)
- , . , , . - . . , , . , , , (NULL) snum. sname . , . , , .
|
|
. , , , , . , 24, :
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;
Serres | Cisneros | 0.1300 | |
Serres | Liu | 0.1300 | |
Rifkin | NO MATCH | 0.1500 | |
Peel | Clemens | 0.1200 | |
Peel | Hoffman | 0.1200 | |
Motika | Clemens | 0.1100 | |
Motika | Hoffman | 0.1100 | |
Axelrod | NO MATCH | 0.1000 |
24
' 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;
25 .
Serres | an Jose | MATCHED | |
Rifkin | Barselona | NO MATCH | |
Peel | London | MATCHED | |
Motika | London | MATCHED | |
Axelrod | New York | NO MATCH |
25
, . . , ( 26):
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;
San Jose | CUSTOMER - MATCHED | |
San Jose | CUSTOMER - MATCHED | |
Rome | CUSTOMER - NO MATCH | |
Rome | CUSTOMER - NO MATCH | |
New York | SALESPERSON - MATCHED | |
New York | SALESPERSON - NO MATCH | |
London | CUSTOMER - MATCHED | |
London | CUSTOMER - MATCHED | |
Berlin | CUSTOMER - NO MATCH | |
Barcelona | SALESPERSON - MATCHED | |
Barcelona | SALESPERSON - NO MATCH |
26
(, 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);
, .
1. EXISTS 300.
2. ?
3. EXISTS .
4. ( ) (: - ).
5. (ANY) Serres.
6. ?
7. ANY ALL, .
8. ( ) .
9. - MAX.
10. , , . rating=200 , - " ", " ".
11. . .
12. . snum San Jose; , cnum San Jose; onum 3 . , . (: , . .)
:
1. ?
2. UNION?
3. ?
4. ANY?
- MySQL ; .: , 2009. - 621 c.
- , MySQL. ; .: , 2010. - 624 c.
- , .; , . Microsoft SQL Server 2000 21 (+ CD-ROM); .: , 2013. - 549 c.
- , ; , Microsoft Access VBA; .: , 2013. - 416 c.
- , .. . ; ; -, 2013. - 399 c.
- , .. . . Microsoft Access; , 2012. - 613 c.
- , .. ; .: ; 6-, 2012. - 360 c.
- , Access 2000 . ; .: , 2012. - 376 c.
- -, , Microsoft Access 2003; .: , 2010. - 416 c.
- , ..; , ..; , .. . ; .: , 2010. - 352 c.
- , ..; , .. ; , 2011. - 602 c.
- , .. Microsoft Office Access 2007: ; , 2008. - 464 c.
- , SQL; : , 2009. - 539 c.
- , .. ; , 2012. - 420 c.
- , ; , ; , Microsoft Office Access 2007 ""; .: , 2012. - 384 c.
- , . ; .: , 2011. - 453 c.
|
|