如何实现数据库的行列转换，原作者fly115

最新推荐文章于 2024-08-16 10:08:32 发布

bfox

最新推荐文章于 2024-08-16 10:08:32 发布

阅读量924

点赞数

文章标签：数据库 null sql 报表算法 c

行列转换实例

表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

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 );

-- --------

说明：

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)

　　　　 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

部门b 3 1 2 0 1 0

部门c 1 1 0 0 0 0

部门d 3 1 2 0 0 1

部门e 1 1 0 0 0 0

部门f 1 1 0 1 0 0

如何实现数据库的行列转换， 原作者fly115

如何实现数据库的行列转换，原作者fly115