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

    Trackback： http://tb.blog.csdn.net/TrackBack.aspx？PostId=1476118

查看本文来源