【例6.26】从样例数据库pubs的authors表中查询名字(au_fname列)以M开头的作者的名(au_fname列)和姓(au_lname列),并增加一个类型列(列名为type),列的内容为Author;从样例数据库pubs的employee表中查询名字(fname列)以M开头的雇员的名(fname列)和姓(lnam列),并增加一个列,列的内容为Employee;最后将两个查询的结果合并在一起。
USE pubs
SELECT au_fname,au_lname,'Author' as type FROM authors WHERE au_fname LIKE 'M%'
UNION
SELECT fname,lname,'Employee' FROM employee WHERE fname LIKE 'M%'
运行结果:
au_fname au_lname type
-------------------- -----------------------------
Manuel Pereira Employee
Margaret Smith Employee
Maria Larsson Employee
Maria Pontes Employee
Marjorie Green Author
Martin Sommer Employee
Martine Rance Employee
Mary Saveley Employee
Matti Karttunen Employee
Meander Smith Author
Michael O'Leary Author
Michel DeFrance Author
Miguel Paolino Employee
Morningstar Greene Author
(所影响的行数为 14 行)
【例6.27】从样例数据库pubs的authors表中查询名字(au_fname列)以M开头的作者的名(au_fname列)和姓(au_lname列),并增加一个类型列(列名为type),列的内容为Author;从样例数据库pubs的employee表中查询名字(fname列)以M开头的雇员的名(fname列)和姓(lnam列),并增加一个列,列的内容为有Employee;最后将两个查询的结果合并在一起,并按类型(type列)的升序进行排序。
USE pubs
SELECT au_fname,au_lname,'Author' as type FROM authors WHERE au_fname LIKE 'M%'
UNION
SELECT fname,lname,'Employee' FROM employee WHERE fname LIKE 'M%'
ORDER BY type
运行结果:
au_fname au_lname type
-------------------- ------------------------------
Marjorie Green Author
Meander Smith Author
Michael O'Leary Author
Michel DeFrance Author
Morningstar Greene Author
Manuel Pereira Employee
Margaret Smith Employee
Maria Larsson Employee
Maria Pontes Employee
Martin Sommer Employee
Martine Rance Employee
Mary Saveley Employee
Matti Karttunen Employee
Miguel Paolino Employee
(所影响的行数为 14 行)
可以看到,此例中UNION子句将两个查询语句的查询结果合并在了一起,并按类型(type列)的升序进行了排序。
USE pubs
SELECT au_fname,au_lname,'Author' as type FROM authors WHERE au_fname LIKE 'M%'
UNION
SELECT fname,lname,'Employee' FROM employee WHERE fname LIKE 'M%'
运行结果:
au_fname au_lname type
-------------------- -----------------------------
Manuel Pereira Employee
Margaret Smith Employee
Maria Larsson Employee
Maria Pontes Employee
Marjorie Green Author
Martin Sommer Employee
Martine Rance Employee
Mary Saveley Employee
Matti Karttunen Employee
Meander Smith Author
Michael O'Leary Author
Michel DeFrance Author
Miguel Paolino Employee
Morningstar Greene Author
(所影响的行数为 14 行)
【例6.27】从样例数据库pubs的authors表中查询名字(au_fname列)以M开头的作者的名(au_fname列)和姓(au_lname列),并增加一个类型列(列名为type),列的内容为Author;从样例数据库pubs的employee表中查询名字(fname列)以M开头的雇员的名(fname列)和姓(lnam列),并增加一个列,列的内容为有Employee;最后将两个查询的结果合并在一起,并按类型(type列)的升序进行排序。
USE pubs
SELECT au_fname,au_lname,'Author' as type FROM authors WHERE au_fname LIKE 'M%'
UNION
SELECT fname,lname,'Employee' FROM employee WHERE fname LIKE 'M%'
ORDER BY type
运行结果:
au_fname au_lname type
-------------------- ------------------------------
Marjorie Green Author
Meander Smith Author
Michael O'Leary Author
Michel DeFrance Author
Morningstar Greene Author
Manuel Pereira Employee
Margaret Smith Employee
Maria Larsson Employee
Maria Pontes Employee
Martin Sommer Employee
Martine Rance Employee
Mary Saveley Employee
Matti Karttunen Employee
Miguel Paolino Employee
(所影响的行数为 14 行)
可以看到,此例中UNION子句将两个查询语句的查询结果合并在了一起,并按类型(type列)的升序进行了排序。