现有两个表t1,t2
t1(report_sec,mms_direction,virus_number)
t2(report_sec,lac,ci...)想让要t1中的report_sec,mms_direction和virus_number字段求和
t2是个count值起个别名为session_number想要的最终结果是
report_sec mms_direction/session_number sum(virus_number)这样的sql语句该怎么写呢?请教高手了,谢谢!
t1(report_sec,mms_direction,virus_number)
t2(report_sec,lac,ci...)想让要t1中的report_sec,mms_direction和virus_number字段求和
t2是个count值起个别名为session_number想要的最终结果是
report_sec mms_direction/session_number sum(virus_number)这样的sql语句该怎么写呢?请教高手了,谢谢!
insert into t2 select report_sec, mms_direction/session_number,sum(virus_number)
from t1;--其实我没太看明白 你的需求。呵呵
select report_sec,
mms_direction,
(select count(*) from t2 where report_sec=t1.report_sec group by report_sec) as session_number,
sum(virus_number)
from t1
group by report_sec
1、count出session_number后,我还需要对其求和
2、group by中只想有report_sec,不希望有mms_direction,但最后查询出的字段中还得有mms_direction
3、我最后需要查询出的四个字段是report_sec,mms_direction,sum(session_number),sum(virus_number)
FROM
(SELECT REPORT_SEC,MIN(MMS_DIRECTION) MMS_DIRECTION,SUM(VIRUS_NUMBER) S_VIRUS_NUMBER FROM T1 GROUP BY REPORT_SEC) A,
(SELECT REPORT_SEC,COUNT(*) SESSION_NUMBER FROM T2 GROUP BY REPORT_SEC) B
WHERE A.REPORT_SEC = B.REPORT_SEC(+) ;
select tb1.report_sec,t1.mms_direction,tb1.virus_number,tb1.session_number
(select t1.report_sec,
sum(t1.virus_number) virus_number,
(select count(*) from t2 where report_sec=t1.report_sec group by report_sec) session_number,
from t1
group by report_sec) tb1,t1
where tb1.report_sec=t1.report_sec
from
(select t1.report_sec,
sum(t1.virus_number) virus_number,
(select count(*) from t2 where report_sec=t1.report_sec group by report_sec) session_number
from t1
group by report_sec) tb1,t1
where tb1.report_sec=t1.report_sec