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