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