2016-07-06 使用 rownum 排序遇到部分列为 null 的问题

问题

一个貌似没问题的 sql:

select * from (
select tt.*
  from (select ta.NID nid,
               ta.VCNAME vcname
          from Zzwx_Wap_Lesson ta
         where 1=1
         and ta.nuseflag = 99
         and ta.ndelflag = 0
           and ta.nid in (select t.nid
                            from Zzwx_Wap_Lesson t
                           where t.tagid like '%282,%')
         order by ta.dupdatetime desc) tt)

查询结果中vcname 字段是有数据的, 但是一旦使用了rownum, 这一列的值就变成了 null

select * from (
select rownum,tt.*
  from (select ta.NID nid,
               ta.VCNAME vcname
          from Zzwx_Wap_Lesson ta
         where 1=1
         and ta.nuseflag = 99
         and ta.ndelflag = 0
           and ta.nid in (select t.nid
                            from Zzwx_Wap_Lesson t
                           where t.tagid like '%282,%')
         order by ta.dupdatetime desc) tt)
;

或者

select * from (
select tt.*
  from (select ta.NID nid,
               ta.VCNAME vcname
          from Zzwx_Wap_Lesson ta
         where 1=1
         and ta.nuseflag = 99
         and ta.ndelflag = 0
           and ta.nid in (select t.nid
                            from Zzwx_Wap_Lesson t
                           where t.tagid like '%282,%')
         order by ta.dupdatetime desc) tt)
where rownum<20
;

解决

使用 row_number() , 关于 rownum 和 row_number() 的区别, 参考这里
所以上面的 sql 应该是这样:

select * from (
select tt.*
  from (select ta.NID nid,
               ta.VCNAME vcname, row_number() over(order by ta.dupdatetime desc) as row2
          from Zzwx_Wap_Lesson ta
         where 1=1
         --and ta.nuseflag = 99
          -- and ta.ndelflag = 0
           and ta.nid in (select t.nid
                            from Zzwx_Wap_Lesson t
                           where t.tagid like '%282,%')
         ) tt)
where row2<20

参考

Some columns nulled when using rownum with data from an inner select

2016-07-06 00:007