以下sql環(huán)境都是在 oracle 11g/scott完成
與GROUP BY一起使用的關(guān)建字 GROUPING,GROUP SET,ROLLUP,CUBE結(jié)合這些特性的函數(shù)可以實(shí)現(xiàn)SQL的明細(xì)+分組統(tǒng)計(jì)
GROUPING 記錄是對哪個(gè)字段進(jìn)行統(tǒng)計(jì),其值只能是 0 & 1
Group(column) =0 表示此字段參與了分組統(tǒng)計(jì) =1表示字段未參與分組統(tǒng)計(jì)
select deptno,job,sum(sal),grouping(deptno),grouping(job)
from emp groupby ( deptno,job);
執(zhí)行結(jié)果如下
select deptno,job,sum(sal),grouping(deptno),grouping(job)
from emp groupby rollup( deptno,job);
等價(jià)于
selectnull,null,sum(sal)from emp
unionall
select deptno,null,sum(sal)from empgroupby deptno
unionall
select deptno,job,sum(sal)from empgroupby deptno,job
首先是進(jìn)行無字段的聚合,然后在對字段進(jìn)行從左到右依次組合后聚合
從上面的結(jié)果可以看到
rollup( deptno,job) = group by deptno, job + group by deptno + group by null
另外 rollup (deptno,job)<>rollup(job,deptno)兩者的結(jié)果集是不同的,可以把SQL修改后,執(zhí)行下看看
提示:在對rollup的分組進(jìn)行組合使用
select deptno,job,sum(sal),grouping (deptno),grouping(job)
from emp groupby rollup ((deptno,job),null) ;
此時(shí) rollup對(deptno,job)這個(gè)組合字段進(jìn)行分組,這樣我們可以控制rollup的分組規(guī)則的顆粒細(xì)度
rollup ((deptno,job),null) =group by (deptno,job)
+ group by (deptno,job), null
+group by null
其中group by (deptno,job)=group by (deptno,job),null
所以其產(chǎn)生的結(jié)果集會出現(xiàn)group by (deptno,job)的重復(fù)記錄
select deptno,job,sum(sal),grouping (deptno),grouping(job)
from emp groupby cube( deptno,job);
等價(jià)于
select deptno,job,sum(sal)from empgroupby deptno,job
unionall
select deptno,null,sum(sal)from empgroupby deptno
unionall
selectnull,null,sum(sal)from emp
union all
select null,job,sum(sal) from emp group by job
CUBE的語法規(guī)則與rollup一致,只是在分組聚合上cube要更復(fù)雜,從數(shù)學(xué)的角度講一個(gè)是排序規(guī)則,一個(gè)是組合規(guī)則
可以按自己定義的規(guī)則進(jìn)行分組聚合,Grouping sets ()的聚合規(guī)則是按“()”里的字段(字段可以是表的單一字段,也可以是多個(gè)字段的組合)進(jìn)行聚合
select deptno,job,sum(sal),grouping (deptno),grouping(job)
from emp groupby grouping sets(deptno,job,null) ;
等價(jià)于 group by depnto + group by job +group by null
select deptno,job,sum(sal),grouping (deptno),grouping(job)from emp group by grouping sets((deptno,job),null) ;
等價(jià)于 group by deptno,job +group by null
select deptno,job,sum(sal),grouping (deptno),grouping(job)
from emp groupby groupingsets ((deptno,job)) ;
等價(jià)與 group by deptno,job
CONNECT BY 語句特有的函數(shù)
SYS_CONNECT_BY_PATH 獲取節(jié)點(diǎn)在整個(gè)樹結(jié)構(gòu)的路徑
CONNECT_BY_ROOT, 獲取節(jié)點(diǎn)規(guī)屬的根節(jié)點(diǎn)
CONNECT_BY_ISLEAF, 判斷該節(jié)點(diǎn)是否存在子節(jié)點(diǎn)
CONNECT_BY_ISCYCLE 該節(jié)點(diǎn)是否循環(huán)
LEVEL 偽列,用來指定節(jié)點(diǎn)在樹結(jié)構(gòu)里的層次
對 CONNECT BY的結(jié)果集的每層進(jìn)行排序
SELECT ……. FROM TABLENAME
CONNECT BY PRIOR ID = F_ID
START WITH F_ID = 0
ORDER SIBLINGS BY ID DESC
具體的SQL語法不在此舉例
產(chǎn)生一個(gè)無限序列
select levelfrom dualconnect by 1=1
顯示20個(gè)記錄結(jié)果集
select levelfrom dualconnect by 1=1 and level<=20
Lpad與 level偽列可以顯示一個(gè)層級結(jié)構(gòu)的數(shù)據(jù)
select lpad(' ',(level-1)*10,' ')||ename,levelfrom emp connectby prior empno=mgr start with mgr is null
注意 where 1=1 and ….. connect by prior and …. start with and ….. 的條件and的約束范圍
再分別執(zhí)行以下語句,并理解其含義
select prior empno,prior mgr, empno ,mgr,x.ename,lpad(' ',(level-1)*10,' ')||x.ename
from emp x connectbyprior x.empno=x.mgrstartwith x.mgrisnull
無限記錄
select *from emp where empno=7369connectby empno=empno;
一條記錄
select *from emp where empno=7369connectby empno=empnoand prior empno is null;
內(nèi)部自循環(huán),無法執(zhí)行
select *from emp where empno=7369connectby prior empno=empno;
一條記錄
select *from empwhere empno=7369connectbyprior empno=empno and prior empnoisnull;
一條記錄
select *from emp where empno=7369connectby nocycle prior empno=empno ;
分析函數(shù) OVER (PARTITION BY …… ORDER BY ……)
PARTITION BY 分組
ORDER BY 排序
ROW_NUMBER ：返回有序組中一行的偏移量，從而可用于按特定標(biāo)準(zhǔn)排序的行號,排行值相同,也是顯示不間斷流水號
RANK ：根據(jù)ORDER BY子句中表達(dá)式的值，從查詢返回的每一行，計(jì)算它們與其它行的相對位置,如果排序值相同,rank()值相同
DENSE_RANK ：根據(jù)ORDER BY子句中表達(dá)式的值，從查詢返回的每一行，計(jì)算它們與其它行的相對位置,與rank相似,只是不會跳號
SUM ：該函數(shù)計(jì)算組中表達(dá)式的累積和
MIN ：在一個(gè)組中的數(shù)據(jù)窗口中查找表達(dá)式的最小值
MAX ：在一個(gè)組中的數(shù)據(jù)窗口中查找表達(dá)式的最大值
AVG ：用于計(jì)算一個(gè)組和數(shù)據(jù)窗口內(nèi)表達(dá)式的平均值。
COUNT ：對一組內(nèi)發(fā)生的事情進(jìn)行累積計(jì)數(shù)
FIRST ：從DENSE_RANK返回的集合中取出排在最前面的一個(gè)值的行
LAST ：從DENSE_RANK返回的集合中取出排在最后面的一個(gè)值的行
FIRST_VALUE ：返回組中數(shù)據(jù)窗口的第一個(gè)值
LAST_VALUE ：返回組中數(shù)據(jù)窗口的最后一個(gè)值。
LAG ：可以訪問結(jié)果集中的其它行而不用進(jìn)行自連接,進(jìn)行銼行顯示,向下銼行
LEAD ：LEAD與LAG相反，LEAD可以訪問組中當(dāng)前行之后的行
例:
select empno,sal,
row_number() over(order by sal) x,
rank() over(order by sal) y,
dense_rank() over(order by sal) z
from emp where deptno=30;
select empno,ename,sal,deptno,
row_number()over(partitionby deptno orderby sal) x,
rank() over(partitionby deptno orderby sal) y,
dense_rank()over(partitionby deptno orderby sal) z
from emp;
執(zhí)行以下SQL,看看查詢后的結(jié)果集
1.
select empno, ename, deptno, sal,
sum(sal) over (partition by deptno order by ename) x
from emp;
--注意PARTITION BY, ORDER BY
先按 deptno進(jìn)行分組,然后在按ename排序后,累加 sal
2.
select empno, ename, deptno, sal,
sum(sal) over (partition by deptno order by ename
rows between unbounded preceding and current row) x
from emp;
--注意ROWS BETWEEN unbounded preceding AND current row
--是指第一行至當(dāng)前行的匯總
--默認(rèn)的分析函數(shù)的累加方式也是從第一行開始到當(dāng)前行
3.
select empno, ename, deptno, sal,
sum(sal) over (partition by deptno order by ename
rows between current row and unbounded following) x
from emp;
--注意ROWS BETWEEN current row AND unbounded following
--是指當(dāng)前行到最后一行的匯總
4.
select empno, ename, deptno, sal,
sum(sal) over (partition by deptno order by ename
rows between 1 preceding and current row) x
from emp;
--注意ROWS BETWEEN 1 preceding AND current row
--是指當(dāng)前行的上一行(rownum-1)到當(dāng)前行的匯總
5.
select empno, ename, deptno, sal,
sum(sal) over (partition by deptno order by ename
rows between 1 preceding and 2 following) x
from emp;
--注意ROWS BETWEEN 1 preceding AND 2 following
--是指當(dāng)前行的上一行(rownum-1)到當(dāng)前行的下輛行(rownum+2)的匯總
Listagg 函數(shù)是11g以上才出現(xiàn)的,針對行轉(zhuǎn)列的函數(shù)(10g不支持改函數(shù))
listagg函數(shù)的語法結(jié)構(gòu)如下：
LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]
listagg雖然是聚合函數(shù)，但可以提供分析功能（比如可選的OVER()子句）。使用listagg中，下列中的元素是必須的：
需要聚合的列或者表達(dá)式
WITH GROUP 關(guān)鍵詞
分組中的ORDER BY子句
listagg聚合的結(jié)果列大小限制在varchar2類型的最大值內(nèi) varchar2(4000)
select deptno,listagg(ename,',')withingroup(orderbynull )over(partitionby deptno) from emp;
從上面的結(jié)果集看,listagg并沒有去掉重復(fù)的數(shù)據(jù)
select deptno, listagg(ename,',')within group(orderbynull)
from emp
groupby deptno;
針對 11g以下的數(shù)據(jù)庫,不能使用 listagg這個(gè)函數(shù),可以進(jìn)行自定義聚合函數(shù)來替代此函數(shù)
oracle自定義聚集函數(shù)接口:
static function ODCIAggregateInitialize(sctx IN OUTstring_agg_type) return number
自定義聚集函數(shù)初始化設(shè)置,從這兒開始一個(gè)聚集函數(shù)
member function ODCIAggregateIterate(self IN OUT string_agg_type ,value IN varchar2) return number
自定義聚集函數(shù),最主要的步驟,這個(gè)函數(shù)定義我們的聚集函數(shù)具體做什么操作,后面的例子,是取最大值,最小值,平均值,還是做連接操作.self為當(dāng)前聚集函數(shù)的指針,用來與前面的計(jì)算結(jié)果進(jìn)行關(guān)聯(lián)
member function ODCIAggregateMerge (self IN string_agg_type,returnValue OUT varchar2,flags IN number) return number
用來合并兩個(gè)聚集函數(shù)的兩個(gè)不同的指針對應(yīng)的結(jié)果,用戶合并不同結(jié)果結(jié)的數(shù)據(jù),特別是處理并行(parallel)查詢聚集函數(shù)的時(shí)候.
member function OCDIAggregateTerminate(self IN string_agg_type,returnValue OUT varchar2,flags IN number) return number
終止聚集函數(shù)的處理,返回聚集函數(shù)處理的結(jié)果.
下面代碼是實(shí)現(xiàn)自定義函數(shù)的結(jié)果如下圖所示
實(shí)現(xiàn)代碼如下:
create or replace type cux_listagg as object (
--定義變量
result_string varchar2(4000),
--自定義聚集函數(shù)初始化設(shè)置,從這兒開始一個(gè)聚集函數(shù)
static function ODCIAggregateInitialize(cs_ctx In Out cux_listagg) return number,
--自定義聚集函數(shù),最主要的步驟,這個(gè)函數(shù)定義我們的聚集函數(shù)具體做什么操作,后面的例子,是取最大值,最小值,平均值,還是做連接操作
--self 為當(dāng)前聚集函數(shù)的指針,用來與前面的計(jì)算結(jié)果進(jìn)行關(guān)聯(lián)
member function ODCIAggregateIterate(self In Out cux_listagg,value in varchar2) return number,
-- 用來合并兩個(gè)聚集函數(shù)的兩個(gè)不同的指針對應(yīng)的結(jié)果,用戶合并不同結(jié)果結(jié)的數(shù)據(jù),特別是處理并行(parallel)查詢聚集函數(shù)的時(shí)候.
member function ODCIAggregateMerge(self In Out cux_listagg,ctx2 In Out cux_listagg) return number,
--終止聚集函數(shù)的處理,返回聚集函數(shù)處理的結(jié)果.
member function ODCIAggregateTerminate(self In Out cux_listagg,returnValue Out varchar2,flags in number) return number
)
/
create or replace type body cux_listagg is
--自定義聚集函數(shù)初始化設(shè)置,從這兒開始一個(gè)聚集函數(shù)
static function ODCIAggregateInitialize(cs_ctx IN OUT cux_listagg) return number
is
begin
cs_ctx := cux_listagg(null);
return ODCIConst.Success;
end;
--自定義聚集函數(shù),最主要的步驟,這個(gè)函數(shù)定義我們的聚集函數(shù)具體做什么操作,后面的例子,是取最大值,最小值,平均值,還是做連接操作
--self 為當(dāng)前聚集函數(shù)的指針,用來與前面的計(jì)算結(jié)果進(jìn)行關(guān)聯(lián)
member function ODCIAggregateIterate(self IN OUT cux_listagg,
value IN varchar2 )
return number
is
begin
self.result_string := self.result_string || ','|| value;
--此處用來處理聚合的數(shù)據(jù)邏輯,本例是進(jìn)行字符串的拼連,
return ODCIConst.Success;
end;
--終止聚集函數(shù)的處理,返回聚集函數(shù)處理的結(jié)果.
member function ODCIAggregateTerminate(self IN Out cux_listagg,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := ltrim(rtrim(self.result_string,','),',');
return ODCIConst.Success;
end;
-- 用來合并兩個(gè)聚集函數(shù)的兩個(gè)不同的指針對應(yīng)的結(jié)果,用戶合并不同結(jié)果結(jié)的數(shù)據(jù),特別是處理并行(parallel)查詢聚集函數(shù)的時(shí)候.
member function ODCIAggregateMerge(self IN OUT cux_listagg,
ctx2 IN Out cux_listagg)
return number
is
begin
self.result_string := self.result_string || ',' || ctx2.result_string;
return ODCIConst.Success;
end;
end;
/
再對這個(gè)type進(jìn)行函數(shù)的創(chuàng)建
CREATE or replace FUNCTION f_row_column(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING cux_listagg;
/
執(zhí)行以下語句即可
select deptno, f_row_column(ename)from scott.emp
groupby deptno
利用connect by+分析函數(shù)也可以完成行轉(zhuǎn)列的功能
請參考如下SQL
with tas
(select deptno,
ename,
count(*)over(partitionby deptno) count_num,
row_number()over(partitionby deptno orderbynull) row_num
from emp)
select deptno, substr(sys_connect_by_path(ename,','),2) row_column
from t where count_num=row_num
connectbyprior t.row_num+1 = t.row_num and t.deptno =prior t.deptno
startwith t.row_num =1
條件說明:
connectbyprior t.row_num+1 = t.row_num and t.deptno =prior t.deptno
Prior t.row_num +1=t.row_num 進(jìn)行自連接的層次構(gòu)造
t.deptno= prior t.deptno 保證自連接的記錄都在一個(gè)deptno里進(jìn)行
另外很多人使用 wmsys.wm_concat這個(gè)函數(shù)進(jìn)行行列互換這個(gè)函數(shù)是oracle非公布的函數(shù),也就是說在以后的數(shù)據(jù)庫版本中,不一定還會繼續(xù)支持,所以盡量避免使用,最好使用公布的函數(shù)
Regexp_substr 按照正則表達(dá)式的規(guī)則,將一個(gè)字符串按分隔符拆分成記錄行
REGEXP_SUBSTR(srcstr, pattern, position, occurrence, modifier)
srcstr ：檢索字符串
pattern ：匹配模式
position ：搜索srcstr的起始位置（默認(rèn)為1）
occurrence ：搜索第幾次出現(xiàn)匹配模式的字符串（默認(rèn)為1）
modifier ：檢索模式('i'不區(qū)分大小寫進(jìn)行檢索；'c'區(qū)分大小寫進(jìn)行檢索。默認(rèn)為'c'）
select regexp_substr('ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD',
'[^,]+',
1,
level)
from dual
connectby regexp_count('ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD','[^,]+') >=
level;
regexp_substr 中使用了正則表達(dá)式 '[^,]+',具體的正則表達(dá)式的含義,可以參考正則表達(dá)的語法
在不支持regexp_count的數(shù)據(jù)庫環(huán)境下,可以使用 length , replace這兩個(gè)函數(shù)來實(shí)現(xiàn)它的效果
Regexp_count Regexp_like Regexp_replace Regexp_instr這些函數(shù)都可以使用正則表達(dá)式來完成特定模式的處理
Pivot 、Unpivot這兩個(gè)函數(shù)是11g版本后推出的,
pivot ( 聚合函數(shù) for列名 in (列名的值))
unpiovt ( 偽列 for偽列 in (表中的字段名稱 ) )
--表中的字段名稱可以是多個(gè),但必須是類型一致的字段
統(tǒng)計(jì)emp表中每個(gè)部門下的JOB有多少個(gè)員工,并以交叉報(bào)表的形式展現(xiàn)
select *from (
SELECT x.ename, x.deptno,x.job FROM emp x
)
pivot (count(ename)for jobin ('ANALYST','CLERK','MANAGER','PRESIDENT','SALESMAN') )
;
對不能使用pivot的函數(shù)我們可以通過其它方式展現(xiàn),以下SQL是比較常用的產(chǎn)生交叉樣式;
SELECT DEPTNO,SUM(DECODE(JOB,'ANALYST',1,0)) ANALYST
,SUM(DECODE(JOB,'CLERK',1,0)) CLERK
,SUM(DECODE(JOB,'MANAGER',1,0)) MANAGER
,SUM(DECODE(JOB,'PRESIDENT',1,0)) PRESIDENT
,SUM(DECODE(JOB,'SALESMAN',1,0)) SALESMAN
FROM EMP
GROUPBY DEPTNO;
Unpiovt :
select * from emp
unpivot(
valuefor VALUE_TYPEin (ename,job)
);
這個(gè)函數(shù)一般不太常見,但確是非常有用的一個(gè)函數(shù),基本上model可以完成所有函數(shù)的功能
具體的官方資料可以參考 noname.htm
語法定義如下
--MODEL:MODEL語句的關(guān)鍵字，必須,后面可以跟 partition by
--DIMENSION BY:維度的意思,必須,而且必須是一個(gè)主鍵或者是組合主鍵。
--MEASURES:指定作為數(shù)組的列,可以定義出許多有規(guī)則的偽列
--RULES:對數(shù)組的各列進(jìn)行各種操作規(guī)則的定義,特有的函數(shù)有 any,cv(),cv(維度字段)
先從簡單的了解下model函數(shù)的特性:
自循環(huán)功能
selectkey, m_1 from dual
model
dimensionby(0key) --定義維度列名=key值等于0
measures(cast(nullasvarchar2(100)) m_1 ) --定義一個(gè)度量類型是 varchar2(100) 列名=m_1
rules--規(guī)則約束
iterate(5) --定義自循環(huán)次數(shù) =5從 0開始循環(huán)
(m_1[0]=nvl(m_1 [0],'TEST')||'x'||'/'||iteration_number||'/')
利用model的循環(huán)來實(shí)現(xiàn)階層的算法
當(dāng)然,此處不是要真的實(shí)現(xiàn)階乘的算法,只是為了理解model函數(shù)的用法,
再看看如下的SQL
目的:根據(jù)emp表的 mgr和empno的關(guān)系來顯示上級的ename和job
最直接最常用的語法就是
select x.empno,x.ename,x.job,x.mgr,y.ename,y.jobfrom emp x,emp y
where x.mgr=y.empno(+) ;
但這樣的SQL的執(zhí)行計(jì)劃顯示對EMP表進(jìn)行了兩次全表掃描
換成model函數(shù)執(zhí)行下
select *from emp
model
dimensionby (empno)
measures ( ename,job,mgr
,cast(nullasvarchar2(20)) mgr_ename
,cast(nullasvarchar2(20)) mgr_job
)
rules (
mgr_ename[any]=ename[mgr[cv()]]
--cv()代表對當(dāng)前行的維度值
--mgr[cv()]是獲取當(dāng)前維度下的mgr值 ,然后在對 mgr[cv()]進(jìn)行維度的數(shù)據(jù)定位到度量ename也就是當(dāng)前ename的上級ename
,mgr_job[any]=job[mgr[cv()]]
)
再看看它的執(zhí)行計(jì)劃,如下圖:
執(zhí)行以下SQL,看看結(jié)果集,理解model函數(shù)
--顯示部門,年份,當(dāng)前年份匯總sal,上年匯總sal
with tas (select deptno,to_char(emp.hiredate,'yyyy')year,sum(sal) salfrom empgroupby deptno,to_char(emp.hiredate,'yyyy'))
select deptno,year,sal,p_sal
from t
model
dimensionby (deptno,year)
measures (sal,0 p_sal)
rules
(
p_sal[any,any]=sal[cv(),cv(year)-1]
);
--分組 group by deptno合計(jì)
select ename,salesfrom emp
modelpartitionby (deptno)
dimensionby (ename)
measures (sal sales)
rules
(
sales['合計(jì)']=sum(sales)[cv(ename)='合計(jì)']
);
-- x =sal
-- y 只給deptno=30的賦予當(dāng)前sum(sal)
-- z 顯示 sum(sal) where deptno=20
-- m 匯總個(gè)部門的sum(sal)
select deptno,ename,sales,x,y,z,mfrom emp
modelpartitionby (deptno)
dimensionby (ename,deptno dep)
measures (sal sales,0 x,0 y,0 z,0 m)
rules
(
x[any,any]=sum(sales)[cv(),cv()]
,y[any,any]=sales[cv(),30]--注意此處是 30可以不用sum,而不是 cv()=30,cv()=30存在多條記錄
,z[any,any]=sum(sales) [any,cv()=20]
,m[any,any]=sum(sales) [any,any]
);
--部門號,年份,
--sum(sal) group by deptno,year
--sum(sal) group by deptno
--sum(sal) group by null
--sum(sal) group by year
--sum(sal) group by null
with tas (select deptno,to_char(emp.hiredate,'yyyy')year,sum(sal) salfrom empgroupby deptno,to_char(emp.hiredate,'yyyy'))
select deptno,year,sal,p_sal,x,y,m
from t
model
dimensionby (deptno,year)
measures (sal,0 p_sal ,0 x,0 y ,0 m)
rules
(
p_sal[any,any]=sum(sal)[cv(),cv()isnotnull ] --sum(sal) group by deptno
,x[any,any]=sum(sal)[any,cv()isnotnull ] --sum(sal) group by null
,y[any,any]=sum(sal)[cv()isnotnull,cv()] --sum(sal) group by year
,m[any,any]=sum(sal)[cv()isnotnull,any ] --sum(sal) group by null
-- cv() 中如果沒有null的記錄那么 cv() is not null等價(jià)與 any
);
用model函數(shù)產(chǎn)生行轉(zhuǎn)列
字符串='adfd,bere,cf234,4d54d'
select r,z
from dual
model
dimension by (0 x)
measures (cast ('adfd,bere,cf234,4d54d'asvarchar2(200)) y
,cast(nullasvarchar2(1000)) z
,cast(nullasvarchar2(1000)) r --顯示字符串列
) --申明一個(gè)字符串的偽列
rulesiterate(10)--定義循環(huán)100次
--PRESENTV(cell,expr1,expr2)
--如果cell引用的記錄在MODEL子句執(zhí)行以前就存在，那么返回表達(dá)式expr1。如果這條記錄不存在，則返回表達(dá)式expr2
until (presentv( y[instr(y[0],',',1,iteration_number+2)],0,1) = 0 )--循環(huán)退出的條件
( --對字符串進(jìn)行循環(huán)截取操作 y[iteration_number+1]=substr(y[iteration_number],instr(y[iteration_number],',',1)+1)
,r[any]=y[0]
,z[iteration_number]=nvl(substr(y[iteration_number],1,instr(y[iteration_number],',',1)-1),y[iteration_number])
,z[iteration_number+1]=y[iteration_number+1]
)
用model函數(shù)產(chǎn)生 列轉(zhuǎn)行
with tas (
select'abc' xfrom dual
unionall
select'XTZ'from dual
unionall
select'IJM'from dual
unionall
select'KPI'from dual
)
select *from t
model
dimensionby (rownum sn)
measures(cast (x asvarchar2(1000)) x)
rules
iterate (100)
until (presentv( x[ iteration_number+1],1,0 )=0 )
(
x[0]=x[0]||','||x[iteration_number+1]
);
用model函數(shù)產(chǎn)生交叉表格
select DEPTNO,CLERK_JOB,ANALYST_JOB,MANAGER_JOB,PRESIDENT_JOB,SALESMAN_JOBfrom emp
modelpartitionby (deptno)
dimensionby (empno,job)
measures ( ename,cast(nullasvarchar2(1000)) CLERK_JOB
,cast(nullasvarchar2(1000)) ANALYST_JOB
,cast(nullasvarchar2(1000)) MANAGER_JOB
,cast(nullasvarchar2(1000)) PRESIDENT_JOB
,cast(nullasvarchar2(1000)) SALESMAN_JOB
)
rules(
CLERK_JOB[ANY,ANY]= (ENAME[CV(),'CLERK'])
,ANALYST_JOB[ANY,ANY]=(ENAME[CV(),'ANALYST'])
,MANAGER_JOB[ANY,ANY]=(ENAME[CV(),'MANAGER'])
,PRESIDENT_JOB[ANY,ANY]=(ENAME[CV(),'PRESIDENT'])
,SALESMAN_JOB[ANY,ANY]=(ENAME[CV(),'SALESMAN'])
);
