| 网站地图 |
CopyRight © 2004-2007 软讯网络 All Rigths Reserved.
利用Oracle rownum完成行转列一例
一、需求背景：
表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);