利用Oracle rownum完成行转列一例

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