oracle偽列以及偽表-閱讀頁
2025-07-15 16:23本頁面
【正文】 與下面的查詢之間沒有逗號Select ….如查詢銷售部門員工的姓名：with clausewith a asSales39。使用select查詢別名例1：查詢cityInfo表同一省中具有最小id和最大id的城市信息方法1：with sta as ( select province,min(id) minValue,max(id) maxValue from cityInfo group by province)select c.* from cityInfo c,sta where (=) and(= or =)order by 方法2：with sta as ( select province,min(id) minValue,max(id) maxValue from cityInfo group by province)select * from cityInfo c where (province,id) in (select province,minValue from sta) or (province,id) in (select province,maxValue from sta)order by province方法3：with sta as ( select province,min(id) minValue,max(id) maxValue from cityInfo group by province)select c.* from cityInfo c inner join sta on (=) and(= or =)order by 方法4：(感覺速度慢！,也許是因?yàn)橥獠樵冇涗浱啵?with sta as ( select province,min(id) minValue,max(id) maxValue from cityInfo group by province)select c.* from cityInfo c where exists( select 1 from sta where (=) and(= or =) )order by 例2：查詢出部門的總薪水大于所有部門平均總薪水的部門。分析：做這個查詢，首先必須計算出所有部門的總薪水，然后計算出總薪水的平均薪水，再篩選出部門的總薪水大于所有部門總薪水平均薪水的部門。方法1：step1:查詢出部門名和部門的總薪水with dept_costs as( select ,sum() dept_total from dept a,emp b where = group by ), step2:利用上一個with查詢的結(jié)果，計算部門的平均總薪水 avg_costs as( select sum(dept_total)/count(*) dept_avg from dept_costs ) step3:從兩個with查詢中比較并且輸出查詢結(jié)果 select dname,dept_total from dept_costs where dept_total ( select dept_avg from avg_costs ) order by dnamewith step1:查詢出部門名和部門的總薪水dept_costs as( select ,sum() dept_total from dept a,emp b where = group by ),step2:利用上一個with查詢的結(jié)果，計算部門的平均總薪水a(chǎn)vg_costs as( select sum(dept_total)/count(*) dept_avg from dept_costs )step3:從兩個with查詢中比較并且輸出查詢結(jié)果select dname,dept_total from dept_costs where dept_total ( select dept_avg from avg_costs ) order by dname。從上面的查詢可以看出，前面的with查詢的結(jié)果可以被后面的with查詢重用，并且對with查詢的結(jié)果列支持別名的使用，在最終查詢中必須要引用所有with查詢，否則會報錯ora32035錯誤.方法2：with a as( select avg(sum(sal)) as avg_sal from emp group by deptno ) Select * from (select deptno ,sum(sal) as total2 from emp group by deptno ) where total2 (select from a) with a as(select avg(sum(sal)) as avg_sal from emp group by deptno)Select * from (select deptno ,sum(sal) as total2 from emp group by deptno ) where total2 (select from a) 例3：找出平均成績大于各班最小平均成績的班名及其該班平均成績各班最小的平均分with minAvgScore as(select min(avgScore) minValue from ( select class,avg(score) avgScore from studentscore group by class ))select class,avg(score) avgScore from studentscore group by class having avg(score)(select minValue from minAvgScore)例4：一個查詢，如果查詢的結(jié)果行不滿足是10的倍數(shù)，則補(bǔ)空行，直到是查詢出的行數(shù)是10的倍數(shù)。select 10mod(count(*),10) shumu from trademark 返回表trademark 中的總行數(shù)差幾個才為10的倍數(shù)。 10個中connect by可以使用子查詢Oracle 9i 寫法：with t as (select 10mod(count(*),10) shumu from trademark) –查詢比10的倍數(shù)差幾個空行select id,namefrom trademarkunion all空行加進(jìn)去select null,null補(bǔ)空行from all_objects where rownum=(select shumu from
范文總結(jié)相關(guān)推薦
鄂ICP備17016276號-1