select *, max(qEnd - qStart)
from
(select qFileID,qLocus,qTranscript,qLength,sFileId,sLocus,sTranscript,
sLength,qStart,qEnd,sStart,sEnd
from bj10dcmegablast
where (qLocus, qTranscript)
in
(select distinct qLocus, qTranscript
from
(select qLocus, qTranscript, count(distinct sFileID) as counts
from bj10dcmegablast
group by qLocus, qTranscript
having counts > 6) as middle1)) as middle2
group by qLocus,sLocus;
bj10dcmegablast有2千万条记录
我测试了
select qLocus, qTranscript, count(distinct sFileID) as counts
from bj10dcmegablast
group by qLocus, qTranscript
having counts > 6
大概一分钟
测试了
select distinct qLocus, qTranscript
from
(select qLocus, qTranscript, count(distinct sFileID) as counts
from bj10dcmegablast
group by qLocus, qTranscript
having counts > 6) as middle1
大概2分钟
第一个那个复杂的query大概要多少时间啊?
谢谢指教了