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 <B］＝ 1－ sign（ 1＋ sign（A－B））
不能用 - sign（A－B）：因为如果不满足A <b则返回 - 1，而不是0，这样就不能用在字段选择上了
［A <＝B］＝ sign( 1－ sign（A－B））
［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 <B Decode( Sign(A -B), - 1, 1, 0 )

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


>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

[ NextPage ]
另外一个关于成绩的分析例子

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

本文转自高海东博客园博客，原文链接：http://www.cnblogs.com/ghd258/archive/2006/02/10/328603.html，如需转载请自行联系原作者