如果想選擇其他幾個(gè)表中的行或從一個(gè)單一的表作為一個(gè)單獨(dú)的結(jié)果集行的幾個(gè)集會(huì)，那么可以使用的UNION。
UNION在MySQL4.0以上版本才能可以使用。本節(jié)說(shuō)明如何使用它。
假設(shè)有兩個(gè)表，潛在和實(shí)際的客戶列表，供應(yīng)商購(gòu)買耗材合并所有三個(gè)表中的姓名和地址，來(lái)創(chuàng)建一個(gè)單一的郵件列表。UNION提供了一種方法做到這一點(diǎn)。假設(shè)三個(gè)表有以下內(nèi)容：
| mysql> SELECT * FROM prospect;+---------+-------+------------------------+| fname | lname | addr |+---------+-------+------------------------+| Peter | Jones | 482 Rush St., Apt. 402 || Bernice | Smith | 916 Maple Dr. |+---------+-------+------------------------+mysql> SELECT * FROM customer;+-----------+------------+---------------------+| last_name | first_name | address |+-----------+------------+---------------------+| Peterson | Grace | 16055 Seminole Ave. || Smith | Bernice | 916 Maple Dr. || Brown | Walter | 8602 1st St. |+-----------+------------+---------------------+mysql> SELECT * FROM vendor;+-------------------+---------------------+| company | street |+-------------------+---------------------+| ReddyParts, Inc. | 38 Industrial Blvd. || Parts-to-go, Ltd. | 213B Commerce Park. |+-------------------+---------------------+ |
這不要緊，如果所有的三個(gè)表具有不同的列名。下面的查詢演示了如何選擇一下子從三個(gè)表的名稱和地址：
| mysql> SELECT fname, lname, addr FROM prospect-> UNION-> SELECT first_name, last_name, address FROM customer-> UNION-> SELECT company, '', street FROM vendor;+-------------------+----------+------------------------+| fname | lname | addr |+-------------------+----------+------------------------+| Peter | Jones | 482 Rush St., Apt. 402 || Bernice | Smith | 916 Maple Dr. || Grace | Peterson | 16055 Seminole Ave. || Walter | Brown | 8602 1st St. || ReddyParts, Inc. | | 38 Industrial Blvd. || Parts-to-go, Ltd. | | 213B Commerce Park. |+-------------------+----------+------------------------+ |
如果想選擇所有記錄，包括重復(fù)的，請(qǐng)ALL的第一個(gè)UNION關(guān)鍵字：
| mysql> SELECT fname, lname, addr FROM prospect-> UNION ALL-> SELECT first_name, last_name, address FROM customer-> UNION-> SELECT company, '', street FROM vendor;+-------------------+----------+------------------------+| fname | lname | addr |+-------------------+----------+------------------------+| Peter | Jones | 482 Rush St., Apt. 402 || Bernice | Smith | 916 Maple Dr. || Grace | Peterson | 16055 Seminole Ave. || Bernice | Smith | 916 Maple Dr. || Walter | Brown | 8602 1st St. || ReddyParts, Inc. | | 38 Industrial Blvd. || Parts-to-go, Ltd. | | 213B Commerce Park. |+-------------------+----------+------------------------+ |
新聞熱點(diǎn)
疑難解答
圖片精選
