oracle行列互换sql,Oracle--SQL行列转换实战

行列转换实例　　表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