分享
 
 
 

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有