select s.total as 总数, s.total - t.reply as 一小时内未回复 from (select count(1) as total from T_A )s, (select count(*) as reply from T_A a inner join T_B b on a.phonenumber = b.phonenumber and b.calltime >= a.calltime and b.calltime <= a.calltime + 1/24)t
SELECT COUNT(*) 总数, sum(CASE WHEN (p2-p1)*24<=1 THEN 1 ELSE 0 END) '1小时之内有记录的数量'
FROM ( SELECT a.phonenumber,a.calltime p1,b.calltime p2 FROM a LEFT OUTER JOIN b ON a.phonenumber = b.phonenumber )
(select count(1) as total from T_A )s,
(select count(*) as reply from T_A a inner join T_B b
on a.phonenumber = b.phonenumber
and b.calltime >= a.calltime
and b.calltime <= a.calltime + 1/24)t
sum(CASE WHEN (p2-p1)*24<=1 THEN 1 ELSE 0 END) '1小时之内有记录的数量'
FROM (
SELECT a.phonenumber,a.calltime p1,b.calltime p2
FROM a LEFT OUTER JOIN b ON a.phonenumber = b.phonenumber
)