利用Oracle rownum完成行转列一例

核心提示：一、需求背景： 表A：CREATE TABLE T_SMCOMMONMSG （FXH NUMBER，FTYPE NUMBER，利用Oracle rownum完成行转列一例，FMSG_CONTENT VARCHAR2（1024 BYTE））表B：CREATE TABLE T_SMYL_COMMONSND （FXH NUM

一、需求背景：

表A：CREATE TABLE T_SMCOMMONMSG （

FXH NUMBER，

FTYPE NUMBER，

FMSG_CONTENT VARCHAR2（1024 BYTE）

）

表B：CREATE TABLE T_SMYL_COMMONSND （

FXH NUMBER，

FMSG_CONTENT1 VARCHAR2（1024 BYTE），

FMSG_CONTENT2 VARCHAR2（1024 BYTE）

）

表A中的原始记录如下：

FXH FTYPE FMSG_CONTENT

------ -------- --------------

98 0 msg1

99 0 msg2

100 0 msg3

101 0 msg4

表B中的现有记录如下：

FXH FMSG_CONTENT1 FMSG_CONTENT2

------- ---------------- --------------

1 空 空

2 空 空

需求：现要求将表A中的记录转换成如下格式的表B中的记录：

FXH FMSG_CONTENT1 FMSG_CONTENT2

------ ---------------- --------------

1 msg1 　　　msg2

2 msg3 　　　msg4

注：表A是一个包含了多种FTYPE类型的信息表，其中FTYPE=0的信息就是转换的原始信息，序号从98开始

表B是一个包含了一种FTYPE类型的信息表，其序号从1开始

二、解决方案：

1.尝试使用如下SQL语句：

update t_smyl_commonsnd a

set a.fmsg_content1 = (select fmsg_content

from t_smcommonmsg b

　where b.ftype = 0

and mod(b.fxh,2) =0)

错误原因：子查询的结果是一个结果集，不能将结果集赋給一条记录的某个字段

2.尝试使用如下SQL语句：

update t_smyl_commonsnd a

　set a.fmsg_content1 = (select fmsg_content

　from t_smcommonmsg b

　　where b.ftype = 0

　　and mod(b.fxh,2) =0

　　and a.fxh = b.fxh -97)

但执行： select * from t_smyl_commonsnd；结果为：

FXH FMSG_CONTENT1 FMSG_CONTEN2
-------- --------------- --------------
1 msg1
2
3 msg2
4
5 msg3

错误原因：子查询中对与表A中FXH值为偶数的记录，其返回值为NULL，所以偶数行的字段都为空

3.尝试使用如下SQL语句：

--更新字段:FMSG_CONTENT1

update t_smyl_commonsnd a

set a.fmsg_content1 = (select fmsg_content

　　　　 from (select rownum id, fmsg_content

　　from (select fmsg_content

　　　　from t_smcommonmsg

　　　　where ftype = 0

　　　　and mod(fxh, 2) = 0

　　　　order by fxh asc) v1

　) v2

　Where a.fxh = v2.id);

--更新字段:FMSG_CONTENT2

update t_smyl_commonsnd a

　set a.fmsg_content2 = (select fmsg_content

　　　　from (select rownum id, fmsg_content

　　　from (select fmsg_content

　　　　from t_smcommonmsg

　　　　where ftype = 0

　　　　and mod(fxh, 2) = 1

　　　　order by fxh asc) v1

　) v2

　　　where a.fxh = v2.id);

返回结果如下：

FXH FMSG_CONTENT1 FMSG_CONTENT2

----- --------------- ---------------

1 msg1 msg2

2 msg3 msg4

更新成功！

分析：

1）。 找出表A中所有FTYPE=0且FXH为双数的记录，也即是FMSG_CONTENT1字段的目标值

select fmsg_content

from t_smcommonmsg

where ftype = 0

and mod(fxh, 2) = 0

order by fxh asc

2）。給筛选的结果加上Rownum，和表B中的记录一一对应

select rownum id, fmsg_content

　 from (select fmsg_content

from t_smcommonmsg

　 where ftype = 0

and mod(fxh, 2) = 0

order by fxh asc) v1

3）。找出视图V1中ID值和表B的FXH号值对应的记录

select fmsg_content

from (select rownum id, fmsg_content

from (select fmsg_content

from t_smcommonmsg

where ftype = 0

and mod(fxh, 2) = 0

order by fxh asc) v1

) v2

where a.fxh = v2.id

4）。更新记录的值：

update t_smyl_commonsnd a

set a.fmsg_content1 = (select fmsg_content

from (select rownum id, fmsg_content

from (select fmsg_content

from t_smcommonmsg

where ftype = 0

and mod(fxh, 2) = 0

order by fxh asc) v1

) v2

where a.fxh = v2.id);