Oracle--SQL行列转换实战

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

行列转换实例

表ttt有三个字段

seq--序列

jcxm --检查项目

zhi--值

数据分别如下:

seqjcxm zhi

-----------------------

1110.50

1120.21

1130.25

1210.24

1220.30

1230.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

110.500.210.25

120.240.300.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)))

----------

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<B]=1-sign(1+sign(A-B)) 不能用-sign(A-B):因为假如不满足A

[A<=B]=sign(1-sign(A-B))

[AB]=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<B Decode( Sign(A-B), -1, 1, 0 )

A<=B Decode( Sign(A-B), 1, 0, 1 )

AB 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 CDecode( 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 LogADecode( LogA, 0, 1, 0 )(1-Sign(LogA))

LogA and LogBLogA * LogB

LogA or LogBLogA + LogB

LogA xor LogBDecode(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) --月份

sellnumber(10,2) --月销售金额

现有数据为:

2000011000

2000021100

2000031200

2000041300

2000051400

2000061500

2000071600

2001011100

2002021200

2003011300

想要转化为以下结构的数据:

yearchar(4) --年份

------------ ---------------------

month1number(10,2)--1月销售金额

month2number(10,2)--2月销售金额

month3number(10,2)--3月销售金额

month4number(10,2)--4月销售金额

month5number(10,2)--5月销售金额

month6number(10,2)--6月销售金额

month7number(10,2)--7月销售金额

month8number(10,2)--8月销售金额

month9number(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应用

11部门a800男

22部门b900女

33部门a400男

44部门d1400 女

55部门e1200 男

66部门f500男

77部门a300女

88部门d1000 男

99部门d1230 女

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 800THEN 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元

部门a32121 00

部门b31201 02

部门c11000 01

部门d31200 12

部门e11000 01

部门f11010 0

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航