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博客 ” ，链接：https://blog.itpub.net/185279/viewspace-624196/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
ORA-01720
下一篇：
oracle case
请登录后发表评论
登录
全部评论
