分类： Oracle
2011-03-10 14:22:26
1.union与union all
union是合并多个查询的结果集，但是需要去除重复的。
union all 是合并多个查询的结果集，但是不去除重复的。
SQL> select employee_id,first_name,last_name from employees where employee_id>150 and employee_id<160;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
151 David Bernstein
152 Peter Hall
153 Christopher Olsen
154 Nanette Cambrault
155 Oliver Tuvault
156 Janette King
157 Patrick Sully
158 Allan McEwen
159 Lindsey Smith
9 rows selected.
SQL> select employee_id,first_name,last_name from employees where employee_id>150 and employee_id<165;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
151 David Bernstein
152 Peter Hall
153 Christopher Olsen
154 Nanette Cambrault
155 Oliver Tuvault
156 Janette King
157 Patrick Sully
158 Allan McEwen
159 Lindsey Smith
160 Louise Doran
161 Sarath Sewall
162 Clara Vishney
163 Danielle Greene
164 Mattea Marvins
14 rows selected.
SQL> set pagesize 100
SQL> select employee_id,first_name,last_name from employees where employee_id>150 and employee_id<160 union select employee_id,first_name,last_name from employees where employee_id>150 and employee_id<165;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
151 David Bernstein
152 Peter Hall
153 Christopher Olsen
154 Nanette Cambrault
155 Oliver Tuvault
156 Janette King
157 Patrick Sully
158 Allan McEwen
159 Lindsey Smith
160 Louise Doran
161 Sarath Sewall
162 Clara Vishney
163 Danielle Greene
164 Mattea Marvins
14 rows selected.
SQL> select employee_id,first_name,last_name from employees where employee_id>150 and employee_id<160 union all select employee_id,first_name,last_name from employees where employee_id>150 and employee_id<165;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
151 David Bernstein
152 Peter Hall
153 Christopher Olsen
154 Nanette Cambrault
155 Oliver Tuvault
156 Janette King
157 Patrick Sully
158 Allan McEwen
159 Lindsey Smith
151 David Bernstein
152 Peter Hall
153 Christopher Olsen
154 Nanette Cambrault
155 Oliver Tuvault
156 Janette King
157 Patrick Sully
158 Allan McEwen
159 Lindsey Smith
160 Louise Doran
161 Sarath Sewall
162 Clara Vishney
163 Danielle Greene
164 Mattea Marvins
23 rows selected.
2.使用intersect操作符
intersect操作符返回两个查询检索的共有行
SQL> select employee_id,first_name,last_name from employees where employee_id>150 and employee_id<160 intersect select employee_id,first_name,last_name from employees where employee_id>150 and employee_id<165;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
151 David Bernstein
152 Peter Hall
153 Christopher Olsen
154 Nanette Cambrault
155 Oliver Tuvault
156 Janette King
157 Patrick Sully
158 Allan McEwen
159 Lindsey Smith
9 rows selected.
3.使用minus操作符
第一个结果集减去第二个结果集
SQL> select employee_id,first_name,last_name from employees where employee_id>150 and employee_id<165 minus select employee_id,first_name,last_name from employees where employee_id>150 and employee_id<160;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
160 Louise Doran
161 Sarath Sewall
162 Clara Vishney
163 Danielle Greene
164 Mattea Marvins
4.translate()函数
把参数中的A换成U,B换成V,依次类推
SQL> select translate(upper(FIRST_NAME),'ABCDEF','UVWXYZ') from employees where employee_id>=160 and employee_id<165;
TRANSLATE(UPPER(FIRST_NAME),'ABCDEF','UV
----------------------------------------
LOUISY
SURUTH
WLURU
XUNIYLLY
MUTTYU
同时translate函数也可以转换数字
SQL> select translate(12345,12345,67890) from dual;
TRANS
-----
67890
SQL> select translate(12345,54321,67890) from dual;
TRANS
-----
09876
