select user_name,real_name,amountdesc,
max(decode(TO_CHAR(to_date(a.sendtime,'YYYYMMDD'),'iw'),decode(sign(to_char(sysdate,'iw')-4),1,to_char(sysdate,'iw')-4,to_char(sysdate,'iw')-4+53),amount,0)) as 上四周,
max(decode(TO_CHAR(to_date(a.sendtime,'YYYYMMDD'),'iw'),decode(sign(to_char(sysdate,'iw')-3),1,to_char(sysdate,'iw')-3,to_char(sysdate,'iw')-3+53),amount,0)) as 上三周,
max(decode(TO_CHAR(to_date(a.sendtime,'YYYYMMDD'),'iw'),decode(sign(to_char(sysdate,'iw')-2),1,to_char(sysdate,'iw')-2,to_char(sysdate,'iw')-2+53),amount,0)) as 上二周,
max(decode(TO_CHAR(to_date(a.sendtime,'YYYYMMDD'),'iw'),decode(sign(to_char(sysdate,'iw')-1),1,to_char(sysdate,'iw')-1,to_char(sysdate,'iw')-1+53),amount,0)) as 上一周,
max(decode(TO_CHAR(to_date(a.sendtime,'YYYYMMDD'),'iw'),to_char(sysdate,'iw'),amount,0)) as 当前周
from v_stat_xmlamount a
group by user_name,real_name,amountdesc
order by 3,8 desc
max(decode(TO_CHAR(to_date(a.sendtime,'YYYYMMDD'),'iw'),decode(sign(to_char(sysdate,'iw')-4),1,to_char(sysdate,'iw')-4,to_char(sysdate,'iw')-4+53),amount,0)) as 上四周,
max(decode(TO_CHAR(to_date(a.sendtime,'YYYYMMDD'),'iw'),decode(sign(to_char(sysdate,'iw')-3),1,to_char(sysdate,'iw')-3,to_char(sysdate,'iw')-3+53),amount,0)) as 上三周,
max(decode(TO_CHAR(to_date(a.sendtime,'YYYYMMDD'),'iw'),decode(sign(to_char(sysdate,'iw')-2),1,to_char(sysdate,'iw')-2,to_char(sysdate,'iw')-2+53),amount,0)) as 上二周,
max(decode(TO_CHAR(to_date(a.sendtime,'YYYYMMDD'),'iw'),decode(sign(to_char(sysdate,'iw')-1),1,to_char(sysdate,'iw')-1,to_char(sysdate,'iw')-1+53),amount,0)) as 上一周,
max(decode(TO_CHAR(to_date(a.sendtime,'YYYYMMDD'),'iw'),to_char(sysdate,'iw'),amount,0)) as 当前周
from v_stat_xmlamount a
group by user_name,real_name,amountdesc
order by 3,8 desc
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/185279/viewspace-624196/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/185279/viewspace-624196/