Oracle--SQL行列轉換實戰

csbin發表於2006-06-02
OracleSQL
Oracle--SQL行列轉換實戰[@more@]

　　行列轉換例項　　表ttt有三個欄位
　　seq　--序列
　　jcxm --檢查專案
　　zhi　--值
　　
　　資料分別如下：
　　seq　　　jcxm　　　　 zhi
　　-------　　　--------　　　　　--------
　　11　　　　　1　　　　0.50
　　11　　　　　2　　　　0.21
　　11　　　　　3　　　　0.25
　　12　　　　　1　　　　0.24
　　12　　　　　2　　　　0.30
　　12　　　　　3　　　　0.22
　　
　　實現功能　　建立檢視時移動行值為列值
　　
　　create view v_view1
　　as
　　select seq,
　　sum(decode(jcxm,1, zhi)) 檢測專案1,
　　sum(decode(jcxm,2, zhi)) 檢測專案2,
　　sum(decode(jcxm,3, zhi)) 檢測專案3
　　from ttt
　　group by seq;
　　
　　序號 檢測專案1　　檢測專案2　　檢測專案3
　　11　　　　　0.50　　　　0.21　　　　　0.25
　　12　　　　　0.24　　　　0.30　　　　　0.22
　　
　　技巧：　　用THEN中的0和1來進行統計（SUM）
　　
　　jcxm　 zhi
　　----　 ----
　　a　　　　　 1
　　b　　　　　 1
　　a　　　　　 3
　　d　　　　　 2
　　e　　　　　 4
　　f　　　　　 5
　　a　　　　　 5
　　d　　　　　 3
　　d　　　　　 6
　　b　　　　　 5
　　c　　　　　 4
　　b　　　　　 3
　　求他的zhi既是1，也是3，也是5的jcxm
　　方法一
　　select jcxm
　　from ttt
　　group by jcxm
　　having sum(decode(zhi,1,-1,3,-1,5,-1,0)) = -3
　　方法二
　　select jcxm from ttt
　　group by jcxm having (sign(sum(decode(zhi,1,-1,0)))+
　　sign(sum(decode(zhi,3,-1,0)))+sign(sum(decode(zhi,5,-1,0)))<=-3);
　　
　　----------
　　a
　　b
　　說明：
　　sign()函式根據某個值是0、正數還是負數，分別返回0、1、-1
　　所以可以用sign和decode來完成比較欄位大小來區某個欄位
　　select decode(sign(欄位1-欄位2),-1,欄位3,欄位4) from dual;
　　
　　sign是一個對於寫分析SQL有很強大的功能
　　下面我對sign進行一些總結：
　　但屬性student取0和1以外的值，或者student取兩個以上的標法值，問題就不會這麼簡單了
　　解決辦法就是特徵函式（abs(),sign()）
　　
　　常用的特徵演算法
　　［A＝B］＝1－abs（sign（A－B））
　　［A！＝B］＝abs（sign（A－B））
　　［A　　［A 　　［A>B］＝1－sign（1－sign（A－B））
　　［A>＝B］＝sign(1＋sign（A－B））)
　　［NOTα］＝1－d ［α］
　　［αANDb ］＝d ［α］*d ［b ］ (6)
　　［αOR b ］＝sign（d ［α］＋d [b ]）
　　
　　例如：
　　A 　　A<=B　　　　　　　　　　　　 Decode( Sign(A-B), 1, 0, 1 )
　　A>B　　　　　　　　　　　　 Decode( Sign(A-B), 1, 1, 0 )
　　A>=B　　　　　　　　　　　　 Decode( Sign(A-B), -1, 0, 1 )
　　A=B　　　　　　　　　　　　 Decode( A, B, 1, 0 )
　　A between B and C　　　Decode( Sign(A-B), -1, 0, Decode(Sign(A-C), 1, 0, 1 ))
　　A is null　　　　　　　　　　　 Decode(A,null,1,0)
　　A is not null　　　　　　　　 Decode(A,null,0,1)　　　　 A in (B1,B2,...,Bn)　Decode(A,B1,1,B2,1,...,Bn,1,0)
　　nor LogA　　　　　　　　　　Decode( LogA, 0, 1, 0 )　　　　　　　(1-Sign(LogA))
　　LogA and LogB　　　　　　LogA * LogB
　　LogA or LogB　　　　　　　LogA + LogB
　　LogA xor LogB　　　　　　Decode(Sign(LogA),Sign(LogB),0,1)
　　Mod(Sign(LogA),Sign(LogB),2
　　
　　>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
　　
　　另外一個關於成績的分析例子
　　
　　SELECT
　　SUM(CASE WHEN cj <60 THEN 1 ELSE 0 END) as "not passed",
　　SUM(CASE WHEN cj BETWEEN 60 AND 79 THEN 1 ELSE 0 END) as "passed",
　　SUM(CASE WHEN cj BETWEEN 80 AND 89 THEN 1 ELSE 0 END) as "good",
　　SUM(CASE WHEN cj >=90 THEN 1 ELSE 0 END) as "Excellent"
　　FROM cjtable;
　　
　　decode用法2
　　表、檢視結構轉化
　　現有一個商品銷售表sale，表結構為：
　　month　　　 char(6)　　　　　 --月份
　　sell　　　　number(10,2)　　　 --月銷售金額
　　
　　現有資料為：
　　200001　　1000
　　200002　　1100
　　200003　　1200
　　200004　　1300
　　200005　　1400
　　200006　　1500
　　200007　　1600
　　200101　　1100
　　200202　　1200
　　200301　　1300
　　
　　想要轉化為以下結構的資料：
　　year　　　char(4)　　　　　　　 --年份
　　------------　 ---------------------
　　month1　　number(10,2)　　　--1月銷售金額
　　month2　　number(10,2)　　　--2月銷售金額
　　month3　　number(10,2)　　　--3月銷售金額
　　month4　　number(10,2)　　　--4月銷售金額
　　month5　　number(10,2)　　　--5月銷售金額
　　month6　　number(10,2)　　　--6月銷售金額
　　month7　　number(10,2)　　　--7月銷售金額
　　month8　　number(10,2)　　　--8月銷售金額
　　month9　　number(10,2)　　　--9月銷售金額
　　month10　 number(10,2)　　　--10月銷售金額
　　month11　 number(10,2)　　　--11月銷售金額
　　month12　 number(10,2)　　　--12月銷售金額
　　
　　結構轉化的SQL語句為：
　　create or replace view
　　v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
　　as
　　select
　　substrb(month,1,4),
　　sum(decode(substrb(month,5,2),'01',sell,0)),
　　sum(decode(substrb(month,5,2),'02',sell,0)),
　　sum(decode(substrb(month,5,2),'03',sell,0)),
　　sum(decode(substrb(month,5,2),'04',sell,0)),
　　sum(decode(substrb(month,5,2),'05',sell,0)),
　　sum(decode(substrb(month,5,2),'06',sell,0)),
　　sum(decode(substrb(month,5,2),'07',sell,0)),
　　sum(decode(substrb(month,5,2),'08',sell,0)),
　　sum(decode(substrb(month,5,2),'09',sell,0)),
　　sum(decode(substrb(month,5,2),'10',sell,0)),
　　sum(decode(substrb(month,5,2),'11',sell,0)),
　　sum(decode(substrb(month,5,2),'12',sell,0))
　　from sale
　　group by substrb(month,1,4);
　　
　　體會：要用decode /group by/ order by/sign/sum來實現不同報表的生成
　　>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
　　CASE應用
　　
　　1　　　　1　　　　部門a　　　　800　　　　男
　　2　　　　2　　　　部門b　　　　900　　　　女
　　3　　　　3　　　　部門a　　　　400　　　　男
　　4　　　　4　　　　部門d　　　　1400　　　 女
　　5　　　　5　　　　部門e　　　　1200　　　 男
　　6　　　　6　　　　部門f　　　　500　　　　男
　　7　　　　7　　　　部門a　　　　300　　　　女
　　8　　　　8　　　　部門d　　　　1000　　　 男
　　9　　　　9　　　　部門d　　　　1230　　　 女
　　10　　　 10　　　　部門b　　　 2000　　　 女
　　11　　　 11　　　　部門c　　　 2000　　　 男
　　12　　　 12　　　　部門b　　　 1200　　　 男
　　
　　SELECT jcxm as 部門,COUNT(seq) as 人數,
　　SUM(CASE SEX WHEN 1 THEN 1 ELSE 0 END) as 男,
　　SUM(CASE SEX WHEN 2 THEN 1 ELSE 0 END) as 女,
　　SUM(CASE SIGN(zhi-800) WHEN -1 THEN 1 ELSE 0 END) as 小於800元,
　　SUM((CASE SIGN(zhi-800)*SIGN(zhi-1000)　　　　　　　　　　/*用*來實現功能*/
　　WHEN -1 THEN 1 ELSE 0 END)+(CASE zhi
　　WHEN 800　THEN 1 ELSE 0 END)) as 從800至999,　　　　 /*注意別名不能以數字開頭*/
　　SUM((CASE SIGN(zhi-1000)*SIGN(zhi-1200)
　　WHEN -1 THEN 1 ELSE 0 END)+(CASE zhi
　　WHEN 1000 THEN 1 ELSE 0 END)) as 從1000元至1199元,
　　SUM((CASE SIGN(zhi-1200) WHEN 1 THEN 1 ELSE 0 END)
　　+(CASE zhi WHEN 1200 THEN 1 ELSE 0 END)) as 大於1200元
　　FroM ttt
　　GROUP BY jcxm
　　
　　部門名 人數　　男　　女　 小於800元 從800至999 從1000元至1199元　 大於1200元
　　部門a　3　　　　2　　1　　　　2　　　　1　　　　　 0　　　　　　　　0
　　部門b　3　　　　1　　2　　　　0　　　　1　　　　　 0　　　　　　　　2
　　部門c　1　　　　1　　0　　　　0　　　　0　　　　　 0　　　　　　　　1
　　部門d　3　　　　1　　2　　　　0　　　　0　　　　　 1　　　　　　　　2
　　部門e　1　　　　1　　0　　　　0　　　　0　　　　　 0　　　　　　　　1
　　部門f　1　　　　1　　0　　　　1　　　　0　　　　　 0　　　　　　　　0

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/720091/viewspace-839286/，如需轉載，請註明出處，否則將追究法律責任。

相關文章