select * from virtel_telrecord t where t.sn in (select b.sn from virtel_telrecord b ,virtel_telrecord c where b.source = c.source and b.dest = c.dest and b.sn !=c.sn and b.status = 'Z' and c.end_time = b.start_time)帮帮忙
select b.* from virtel_telrecord b left join virtel_telrecord c on b.source = c.source and b.dest = c.dest where b.sn !=c.sn and b.status = 'Z' and c.end_time = b.start_time
select b.* from virtel_telrecord b , virtel_telrecord c where b.source = c.source and b.dest = c.dest and b.start_time = c.end_time and b.status = 'Z' and b.sn != c.sn --不过最后一个b.sn != c.sn,这个条件是否有点奇怪.
select * --尽量别写*,写全列可以用到索引,而*用不到索引 from virtel_telrecord t where exists(select * --exists 快过 in from virtel_telrecord b ,virtel_telrecord c where b.source = c.source and b.dest = c.dest and b.sn !=c.sn and b.status = 'Z' and t.sn = b.sn)
直接连接就行select b.* from virtel_telrecord b ,virtel_telrecord c where b.source = c.source and b.dest = c.dest and b.sn !=c.sn and b.status = 'Z' and c.end_time = b.start_time
select ENTERPRISE_NUM , SUM(CASE WHEN STATUS = 'A' then 1 else 0 end) , SUM(CASE WHEN STATUS = 'Z' then 1 else 0 end) from VIRTEL_TELRECORD where START_TIME >= '2006-8-2 10:35:12' and sn not in(select b.sn from virtel_telrecord b ,virtel_telrecord c where b.source = c.source and b.dest = c.dest and b.sn !=c.sn and b.status = 'Z' and c.end_time = b.start_time) group by ENTERPRISE_NUM 其实是要优化这个SQL
-- tryselect a.ENTERPRISE_NUM , SUM(CASE WHEN a.status = 'A' then 1 else 0 end) , SUM(CASE WHEN a.status = 'Z' then 1 else 0 end) from virtel_telrecord a left join (select b.sn from virtel_telrecord b ,virtel_telrecord c where b.source = c.source and b.dest = c.dest and b.sn !=c.sn and b.status = 'Z' and c.end_time = b.start_time) b on a.sn = b.sn where a.start_time >= '2006-8-2 10:35:12' and b.sn is null group by a.ENTERPRISE_NUM
on b.source = c.source and b.dest = c.dest
where b.sn !=c.sn and b.status = 'Z'
and c.end_time = b.start_time
where b.source = c.source and
b.dest = c.dest and
b.start_time = c.end_time and
b.status = 'Z' and b.sn != c.sn
--不过最后一个b.sn != c.sn,这个条件是否有点奇怪.
from virtel_telrecord t
where exists(select * --exists 快过 in
from virtel_telrecord b ,virtel_telrecord c
where b.source = c.source and b.dest = c.dest
and b.sn !=c.sn and b.status = 'Z'
and t.sn = b.sn)
where b.source = c.source and b.dest = c.dest
and b.sn !=c.sn and b.status = 'Z'
and c.end_time = b.start_time
---------------------------------------------------
去除 end_time = start_time 的记录
SUM(CASE WHEN STATUS = 'A' then 1 else 0 end) ,
SUM(CASE WHEN STATUS = 'Z' then 1 else 0 end)
from VIRTEL_TELRECORD
where START_TIME >= '2006-8-2 10:35:12'
and sn not in(select b.sn from virtel_telrecord b ,virtel_telrecord c
where b.source = c.source and b.dest = c.dest
and b.sn !=c.sn and b.status = 'Z'
and c.end_time = b.start_time)
group by ENTERPRISE_NUM 其实是要优化这个SQL
SUM(CASE WHEN a.status = 'A' then 1 else 0 end) ,
SUM(CASE WHEN a.status = 'Z' then 1 else 0 end)
from
virtel_telrecord a
left join
(select b.sn from virtel_telrecord b ,virtel_telrecord c
where b.source = c.source and b.dest = c.dest
and b.sn !=c.sn and b.status = 'Z'
and c.end_time = b.start_time) b
on a.sn = b.sn
where a.start_time >= '2006-8-2 10:35:12' and b.sn is null
group by a.ENTERPRISE_NUM