select count(1) from tab2 inner join tab1 on tab1.id=tab2.id where months_between(sysdate,tab2.birthday)/12>65
SELECT COUNT(1)-- COUNT(*)个人习惯是count(1)或者具体的count(fieldName) FROM tab1 INNER JOIN (SELECT id FROM tab2 WHERE months_between(sysdate,tab2.birthday)>65*12 --比较符左边尽量不使用计算 --WHERE months_between(sysdate,tab2.birthday)/12>65 )e ON tab1.id=e.id;
select count(1)
from tab2
inner join tab1 on tab1.id=tab2.id
where months_between(sysdate,tab2.birthday)/12>65
FROM tab1
INNER JOIN
(SELECT id FROM tab2
WHERE months_between(sysdate,tab2.birthday)>65*12
--比较符左边尽量不使用计算
--WHERE months_between(sysdate,tab2.birthday)/12>65
)e
ON tab1.id=e.id;
tab2.birthday < sysdate - INTERVAL '780' MONTH(3)
2,子查询不要用
3,对列不要使用函数,可以对常量使用函数
4,推荐sql:
select id from tab1
intersect
select id from tab2
id要是key,那就等OK了