create view Ta as
select stationno,username,sum(ssll)as 当日流量
from wujiang.hotnetview
where recordtime between to_date('03/07/200301:01:01','dd/mm/yyyyhh24:mi:ss') and to_date('03/07/200323:59:59','dd/mm/yyyyhh24:mi:ss') group by(stationno,username);create view Tb as
select stationno,username,sum(ssll)as 月流量
from wujiang.hotnetview where recordtime between to_date('03/07/2003','dd/mm/yyyy') and to_date('03/08/2003','dd/mm/yyyy')group by(stationno,username);select a.stationno,a.username,a.当日流量,b.月流量
from Ta a,Tb b
where a.stationno=b.stationno and a.username=b.username;
select stationno,username,sum(ssll)as 当日流量
from wujiang.hotnetview
where recordtime between to_date('03/07/200301:01:01','dd/mm/yyyyhh24:mi:ss') and to_date('03/07/200323:59:59','dd/mm/yyyyhh24:mi:ss') group by(stationno,username);create view Tb as
select stationno,username,sum(ssll)as 月流量
from wujiang.hotnetview where recordtime between to_date('03/07/2003','dd/mm/yyyy') and to_date('03/08/2003','dd/mm/yyyy')group by(stationno,username);select a.stationno,a.username,a.当日流量,b.月流量
from Ta a,Tb b
where a.stationno=b.stationno and a.username=b.username;
stationno,username,
sum(decode(to_date(recordtime),to_date('03/07/2003','dd/mm/yyyy'),ssll,0))as 当日流量,
sum(ssll)as 月流量 from wujiang.hotnetview where recordtime between to_date('03/07/2003','dd/mm/yyyy') and to_date('03/08/2003','dd/mm/yyyy')group by(stationno,username)
HQ.Wang 海清
意思就是把 非当天的数据变0
HQ.Wang 海清
(select stationno a1,username a2,sum(ssll) a3 from wujiang.hotnetview where to_char(recordtime,'yyyymmdd')='20030703') tab1,
(select sum(ssll) aa from wujiang.hotnetview where recordtime between to_date('03/07/2003','dd/mm/yyyy') and to_date('03/08/2003','dd/mm/yyyy')) tab2 group by tab1.a1,tab1.a2;
(select stationno,username,sum(ssll)as 当日流量 from
wujiang.hotnetview where recordtime between to_date('03/07/200301:01:01','dd/mm/yyyyhh24:mi:ss') and to_date('03/07/200323:59:59','dd/mm/yyyyhh24:mi:ss') group by(stationno,username) ) a,
(select stationno,username,sum(ssll)as 月流量 from wujiang.hotnetview where recordtime between to_date('03/07/2003','dd/mm/yyyy') and to_date('03/08/2003','dd/mm/yyyy')group by(stationno,username) )b
where a.stationno=b.stationno and a.username=b.username