从外到里：
1、最外层SELECT * FROM （......）B WHERE R >= 1
2、SELECT A.*, ROWNUM R FROM (......)A WHERE ROWNUM <= 8
3、
select a.zwxm,a.sfzh,f.mc hkszd,g.mc sldw,yyb.yyrq,yyb.yyid,yyb.kssj || ' - ' || yyb.jssj yysd,decode(a.slbh, null, a.ywbh, a.slbh) ywbh,h.mc hczt,e.mc ywlb,a.sqrq,a.lxdh_sj lxdh,a.xczjhm zjhm,a.xczjyxqz zjyxq,
case yyb.sfcg
when '0'
then'预约失败'
when '1'
then'预约成功'
when '2'
then'预约处理中'
when '3'
then'预约待核查'
when '4'
then'预约核查失败'
when '5' then
'预约撤销'
end yyjg,
' ' qz1,' ' qz2,
decode(a.zt_tkzd, '0', '窗口取证', '1', '特快专递') zt_tkzd,
a.sjr_xm,a.sjr_dz,a.sjr_yb,a.sjr_lxdh,rownum num from
yw_wssq_hgtsqxxb a,
(select a.yyid, a.ywbh1 ywbh, a.yyrq, a.kssj, a.jssj, a.sfcg from yw_wssq_zhyyxxb a where a.ywbh1 is not null and a.yylx = '10'
union all
select a.yyid, a.ywbh2 ywbh, a.yyrq, a.kssj, a.jssj, a.sfcg from yw_wssq_zhyyxxb a where a.ywbh2 is not nulland a.yylx = '10'
union all
select a.yyid, a.ywbh3 ywbh, a.yyrq, a.kssj, a.jssj, a.sfcg from yw_wssq_zhyyxxb a where a.ywbh3 is not null and a.yylx = '10')yyb,
xt_ywlbb e,dm_xzqhb f,dm_sldwxxb g,dm_wssq_sljgzt h
where a.ywbh = yyb.ywbh(+)and a.ywlb = e.dm(+)and a.hkszd = f.dm(+)and a.sldw = g.dm(+)and a.hczt = h.dm(+)
SQL join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。
1、JOIN 类型，以及它们之间的差异。
内连接
join: 如果表中有至少一个匹配，则返回行
inner join：在表中存在至少一个匹配时，INNER JOIN 关键字返回行。
外连接
left join: 即使右表中没有匹配，也从左表返回所有的行，如果右表中有匹配
right join: 即使左表中没有匹配，也从右表返回所有的行*
full join: 只要其中一个表中存在匹配，就返回行
注释：inner join 与 join是相同的。
对于外连接，Oracle中可以使用“(+)”来表示，9i可以使用LEFT/RIGHT/FULL OUTER JOIN，下面将配合实例一一介绍。
- LEFT OUTER JOIN：左外关联
SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);
等价于
SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id=d.department_id(+);
结果为：所有员工及对应部门的记录，包括没有对 应部门编号department_id的员工记录。
- RIGHT OUTER JOIN：右外关联
SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
等价于
SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id(+)=d.department_id;
- FULL OUTER JOIN：全外关联
SELECT e.last_name, e.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id);
结果为：所有员工及对应部门的记录，包括没有对应部门编号 department_id的员工记录和没有任何员工的部门记录。
外连接:
除了显示匹配相等连接条件的数据外,还可以显示某一个表中无法匹配相等连接条件的记录!
- 左条件(+) = 右条件
左条件所在的表必须严格进行相等连接条件的匹配,而右条件所在的表除了匹配相等连接条件外，还可以显 示无法匹配连接条件的数据!
也称为右外连接.
可以用下 列语句取代:
SELECT...FROM 表1 RIGHT OUTER JOIN 表2 ON 条件;
- 左条件 = 右条件(+)
右条件所在的表必须严格进行相等连接条件的匹配,而左条件所在的表除了匹配相等连接条件外，还可以显示无法匹配连接条件的数据!
也 称为左外连接.
4、表yyb
select a.yyid, a.ywbh1 ywbh, a.yyrq, a.kssj, a.jssj, a.sfcg from yw_wssq_zhyyxxb a where a.ywbh1 is not null and a.yylx = '10'
union all
select a.yyid, a.ywbh2 ywbh, a.yyrq, a.kssj, a.jssj, a.sfcg from yw_wssq_zhyyxxb a where a.ywbh2 is not nulland a.yylx = '10'
union all
select a.yyid, a.ywbh3 ywbh, a.yyrq, a.kssj, a.jssj, a.sfcg from yw_wssq_zhyyxxb a where a.ywbh3 is not null and a.yylx = '10'
union 和 union all操作符
4.1、union 和union all 操作符用于合并两个或多个 SELECT 语句的结果集。
4.2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
4.3、默认地，UNION 操作符选取不同的值，即UNION是去了重的。如果允许重复的值，请使用 UNION ALL。
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
5、
SELECT * FROM
(SELECT A.*, ROWNUM R FROM
(select a.zwxm,a.sfzh,f.mc hkszd,g.mc sldw,yyb.yyrq,yyb.yyid,yyb.kssj || ' - ' || yyb.jssj yysd,decode(a.slbh, null, a.ywbh, a.slbh) ywbh,h.mc hczt,e.mc ywlb,a.sqrq,a.lxdh_sj lxdh,a.xczjhm zjhm,a.xczjyxqz zjyxq,
case yyb.sfcg
when '0'
then'预约失败'
when '1'
then'预约成功'
when '2'
then'预约处理中'
when '3'
then'预约待核查'
when '4'
then'预约核查失败'
when '5' then
'预约撤销'
end yyjg,
' ' qz1,' ' qz2,
decode(a.zt_tkzd, '0', '窗口取证', '1', '特快专递') zt_tkzd,
a.sjr_xm,a.sjr_dz,a.sjr_yb,a.sjr_lxdh,rownum num from
yw_wssq_hgtsqxxb a,
(select a.yyid, a.ywbh1 ywbh, a.yyrq, a.kssj, a.jssj, a.sfcg from yw_wssq_zhyyxxb a where a.ywbh1 is not null and a.yylx = '10'
union all
select a.yyid, a.ywbh2 ywbh, a.yyrq, a.kssj, a.jssj, a.sfcg from yw_wssq_zhyyxxb a where a.ywbh2 is not nulland a.yylx = '10'
union all
select a.yyid, a.ywbh3 ywbh, a.yyrq, a.kssj, a.jssj, a.sfcg from yw_wssq_zhyyxxb a where a.ywbh3 is not null and a.yylx = '10')yyb,
xt_ywlbb e,dm_xzqhb f,dm_sldwxxb g,dm_wssq_sljgzt h
where a.ywbh = yyb.ywbh(+)and a.ywlb = e.dm(+)and a.hkszd = f.dm(+)and a.sldw = g.dm(+)and a.hczt = h.dm(+)) A
WHERE ROWNUM <= 8) B
WHERE R >= 1
