select inouttime, personno, personname, departmentname,DEPARTMENTCODE, inoutflag
from
(select rownum rn, personno, personname, inouttime, departmentname,DEPARTMENTCODE, inoutflag
from SC_INOUT_VALID_VIEW
where inouttime >= to_date('2012-09-14','YYYY-MM-DD HH24:MI:SS')
and inouttime <= to_date('2012-09-15','YYYY-MM-DD HH24:MI:SS'))
where rn in (select MAX(rownum) from SC_INOUT_VALID_VIEW where inouttime >= to_date('2012-09-14','YYYY-MM-DD HH24:MI:SS')
and inouttime <= to_date('2012-09-15','YYYY-MM-DD HH24:MI:SS') group by personno) and inoutflag = '进' and substr(DEPARTMENTCODE,1,2) = '12'; 这个查询的作用是查询出某个部门某一时间段内最后一个状态是“进”的所有人的列表.....80条记录用上面这个句子查用时1.5s,400多万条记录的时候最快也要几分钟,求优化......personno,inouttime,DEPARTMENTCODE已做联合索引
from
(select rownum rn, personno, personname, inouttime, departmentname,DEPARTMENTCODE, inoutflag
from SC_INOUT_VALID_VIEW
where inouttime >= to_date('2012-09-14','YYYY-MM-DD HH24:MI:SS')
and inouttime <= to_date('2012-09-15','YYYY-MM-DD HH24:MI:SS'))
where rn in (select MAX(rownum) from SC_INOUT_VALID_VIEW where inouttime >= to_date('2012-09-14','YYYY-MM-DD HH24:MI:SS')
and inouttime <= to_date('2012-09-15','YYYY-MM-DD HH24:MI:SS') group by personno) and inoutflag = '进' and substr(DEPARTMENTCODE,1,2) = '12'; 这个查询的作用是查询出某个部门某一时间段内最后一个状态是“进”的所有人的列表.....80条记录用上面这个句子查用时1.5s,400多万条记录的时候最快也要几分钟,求优化......personno,inouttime,DEPARTMENTCODE已做联合索引
select inouttime, personno, personname, departmentname,DEPARTMENTCODE, inoutflag
from SC_INOUT_VALID_VIEW t1
where t1.inouttime=(select max(inouttime) from SC_INOUT_VALID_VIEW t2
where t1.personno=t2.personno and trunc(t2.inouttime)=to_date('2012-09-14','yyyy-mm-dd')
and substr(DEPARTMENTCODE,1,2) = '12')--如果每個部門persor唯一,此處可省
and inoutflag='进' and substr(DEPARTMENTCODE,1,2) = '12';
SELECT INOUTTIME,
PERSONNO,
PERSONNAME,
DEPARTMENTNAME,
DEPARTMENTCODE,
INOUTFLAG
FROM (SELECT PERSONNO,
PERSONNAME,
INOUTTIME,
DEPARTMENTNAME,
DEPARTMENTCODE,
INOUTFLAG,
ROW_NUMBER() OVER(PARTITION BY SUBSTR(DEPARTMENTCODE, 1, 2) ORDER BY INOUTTIME DESC) ROW_
FROM SC_INOUT_VALID_VIEW
WHERE INOUTTIME >= TO_DATE('2012-09-14', 'YYYY-MM-DD HH24:MI:SS')
AND INOUTTIME <= TO_DATE('2012-09-15', 'YYYY-MM-DD HH24:MI:SS')
AND INOUTFLAG = '进')
WHERE ROW_ = 1;
4楼的代码在36秒左右。还是我最初的那段SQL语句(不是顶楼那段)最快,原来一直觉得那段语句有问题,后来仔细分析了下,还是正确的,汗~~~~~~~运行不到两秒中就出结果。二楼数据重复的问题是数据里面的记录有问题,库中本来就有重复数据的。那位告诉我怎么像楼上这样把代码帖在“SQL Code”里面?回头我把我的贴上来.......thx~~~~~~
怪事儿,我点“回复”只是跳转到本页下面这个回复内容框,可这里没有按钮,“发帖”那里有的,只好先复制过来这个code标记试下....select rownum rn, personno, personname, departmentname, p_apartmentcode, inoutflag, picture1, picture2, video1, video2, controllerip, inouttime
from
SC_INOUT_VALID_DEPT_VIEW
where inouttime >= to_date('2012-09-14','YYYY-MM-DD HH24:MI:SS')
and inouttime <= to_date('2012-09-15','YYYY-MM-DD HH24:MI:SS')
and (inouttime in
(select MAX(inouttime)
from SC_INOUT_VALID
where inouttime >= to_date('2012-09-14','YYYY-MM-DD HH24:MI:SS')
and inouttime <= to_date('2012-09-15','YYYY-MM-DD HH24:MI:SS')
group by personno))
and inoutflag = 0
and substr(DEPARTMENTCODE,1,2) = '12';