with a as ( select numb,name,row_number() over( partition by numb order by name desc) rowid from fenzu ) select max(case when rowid=2 then name end) name2, max(case when rowid=1 then name end) name, a.numb from a group by a.numb
原文：http://www.cnblogs.com/emmy/p/4305332.html