union all 关于order by ,添加序号，获取某个数据在序列中的位置

2019独角兽企业重金招聘Python工程师标准>>>

order by 数据库默认为sql结束，使用with..as 进行操作，序号使用函数row_number() over(ORDER BY s1.reward desc)

with s1 as
 (select s2.wechat_nickname,
         s2.wechat_photo,
         s1.premium,
         s1.reward,
         (select count(*)
           from (select sum(su.reward) reward,
                        st.shop_user_id shop_user_id,
                        count(st.shop_user_id) count,
                        sum(st.premium) premium
                   from SHOP_ACTIVITY_TRADE st,
                        (select sum(a.reward) reward, b.policyno
                           from SHOP_ACTIVITY_REWARD a, SHOP_ACTIVITY_TRADE b
                          where a.policyno = b.policyno
                          group by b.policyno
                          order by sum(a.reward) desc) su
                  where st.policyno = su.policyno
                  group by st.shop_user_id) s3
          where s3.reward >=
                (select sum(su.reward) reward
                   from SHOP_ACTIVITY_TRADE st,
                        (select sum(a.reward) reward, b.policyno
                           from SHOP_ACTIVITY_REWARD a, SHOP_ACTIVITY_TRADE b
                          where a.policyno = b.policyno
                            and b.shop_user_id = '980'
                          group by b.policyno
                          order by sum(a.reward) desc) su
                  where st.policyno = su.policyno
                  group by st.shop_user_id)) inde 
                  
                  from
                 (select sum(su.reward) reward,
                        st.shop_user_id shop_user_id,
                        count(st.shop_user_id) count,
                        sum(st.premium) premium
                   from SHOP_ACTIVITY_TRADE st,
                        (select sum(a.reward) reward, b.policyno
                           from SHOP_ACTIVITY_REWARD a, SHOP_ACTIVITY_TRADE b
                          where a.policyno = b.policyno
                            and b.shop_user_id = '980'
                          group by b.policyno
                          order by sum(a.reward) desc) su
                  where st.policyno = su.policyno
                  group by st.shop_user_id) s1, shop_activity_user s2
          where s1.shop_user_id = s2.shop_user_id
  ),
s2 as
 (
 select s2.wechat_nickname,
         s2.wechat_photo,
         s1.premium,
         s1.reward,
         row_number() over(ORDER BY s1.reward desc) inde
    from (select sum(su.reward) reward,
                 st.shop_user_id shop_user_id,
                 count(st.shop_user_id) count,
                 sum(st.premium) premium
            from SHOP_ACTIVITY_TRADE st,
                 (select sum(a.reward) reward, b.policyno
                    from SHOP_ACTIVITY_REWARD a, SHOP_ACTIVITY_TRADE b
                   where a.policyno = b.policyno
                   group by b.policyno) su
           where st.policyno = su.policyno 
           group by st.shop_user_id
           order by sum(su.reward) desc) s1,
         shop_activity_user s2
   where s1.shop_user_id = s2.shop_user_id
   order by s1.reward desc
   )

select *
  from s1
union all
select * from s2;

转载于:https://my.oschina.net/u/2400545/blog/795260