從外到里：
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'預(yù)約失敗'
when '1'
then'預(yù)約成功'
when '2'
then'預(yù)約處理中'
when '3'
then'預(yù)約待核查'
when '4'
then'預(yù)約核查失敗'
when '5' then
'預(yù)約撤銷'
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 用于根據(jù)兩個(gè)或多個(gè)表中的列之間的關(guān)系，從這些表中查詢數(shù)據(jù)决采。
1自沧、JOIN 類型，以及它們之間的差異织狐。
內(nèi)連接
join: 如果表中有至少一個(gè)匹配暂幼，則返回行
inner join：在表中存在至少一個(gè)匹配時(shí)筏勒，INNER JOIN 關(guān)鍵字返回行移迫。
外連接
left join: 即使右表中沒有匹配宿接，也從左表返回所有的行诚隙，如果右表中有匹配
right join: 即使左表中沒有匹配，也從右表返回所有的行*
full join: 只要其中一個(gè)表中存在匹配劫扒，就返回行
注釋：inner join 與 join是相同的捐顷。
對(duì)于外連接荡陷，Oracle中可以使用“(+)”來表示，9i可以使用LEFT/RIGHT/FULL OUTER JOIN迅涮，下面將配合實(shí)例一一介紹废赞。
- LEFT OUTER JOIN：左外關(guān)聯(lián)
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);
等價(jià)于
SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id=d.department_id(+);
結(jié)果為：所有員工及對(duì)應(yīng)部門的記錄，包括沒有對(duì) 應(yīng)部門編號(hào)department_id的員工記錄叮姑。
- RIGHT OUTER JOIN：右外關(guān)聯(lián)
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);
等價(jià)于
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：全外關(guān)聯(lián)
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);
結(jié)果為：所有員工及對(duì)應(yīng)部門的記錄唉地，包括沒有對(duì)應(yīng)部門編號(hào) department_id的員工記錄和沒有任何員工的部門記錄据悔。
外連接:
除了顯示匹配相等連接條件的數(shù)據(jù)外,還可以顯示某一個(gè)表中無(wú)法匹配相等連接條件的記錄!
- 左條件(+) = 右條件
左條件所在的表必須嚴(yán)格進(jìn)行相等連接條件的匹配,而右條件所在的表除了匹配相等連接條件外，還可以顯 示無(wú)法匹配連接條件的數(shù)據(jù)!
也稱為右外連接.
可以用下 列語(yǔ)句取代:
SELECT...FROM 表1 RIGHT OUTER JOIN 表2 ON 條件;
- 左條件 = 右條件(+)
右條件所在的表必須嚴(yán)格進(jìn)行相等連接條件的匹配,而左條件所在的表除了匹配相等連接條件外耘沼，還可以顯示無(wú)法匹配連接條件的數(shù)據(jù)!
也 稱為左外連接.
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 操作符用于合并兩個(gè)或多個(gè) SELECT 語(yǔ)句的結(jié)果集群嗤。
4.2菠隆、UNION 內(nèi)部的 SELECT 語(yǔ)句必須擁有相同數(shù)量的列。列也必須擁有相似的數(shù)據(jù)類型狂秘。同時(shí)骇径，每條 SELECT 語(yǔ)句中的列的順序必須相同。
4.3者春、默認(rèn)地既峡，UNION 操作符選取不同的值，即UNION是去了重的碧查。如果允許重復(fù)的值运敢，請(qǐng)使用 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'預(yù)約失敗'
when '1'
then'預(yù)約成功'
when '2'
then'預(yù)約處理中'
when '3'
then'預(yù)約待核查'
when '4'
then'預(yù)約核查失敗'
when '5' then
'預(yù)約撤銷'
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
