select b.USER_NAME,b.TEL
from USER_INFO b
where b.E_DATE<sysdate
group by b.USER_ID
union
select b.user,b.tel
from LOGON_INFO a,USER_INFO b
where a.id=b.id
group by b.id
having max(LOGON_TIME)<sysdate-90
from USER_INFO b
where b.E_DATE<sysdate
group by b.USER_ID
union
select b.user,b.tel
from LOGON_INFO a,USER_INFO b
where a.id=b.id
group by b.id
having max(LOGON_TIME)<sysdate-90
I hava done:Ò»¸ösqlÓï¾äµÄÓÅ»¯£º
×ʼµÄsql£¬ÓÃʱ¼ä5s
select count(*) from (
SELECT
icasuser.userid AS userid, icasuser.rsa AS rsa, icasuser.roleid AS roleid,
icasuser.fname as fname, icasuser.lname as lname, icasuser.centerid as centerid,
icasuser.status as status, icasuser.priv as priv, icasuser.password as password,
icasuser.edate as edate, icasuser.coachid as coachid, icasuser.analystid as analystid,
icasuser.trainerid as trainerid, icasuser.title as title, icasuser.action as action
FROM icasuser, logon_info
WHERE SYSDATE - logon_info.login_time > 90 AND icasuser.userid = logon_info.sbcuid
UNION
SELECT
icasuser.userid AS userid, icasuser.rsa AS rsa, icasuser.roleid AS roleid,
icasuser.fname as fname, icasuser.lname as lname, icasuser.centerid as centerid,
icasuser.status as status, icasuser.priv as priv, icasuser.password as password,
icasuser.edate as edate, icasuser.coachid as coachid, icasuser.analystid as analystid,
icasuser.trainerid as trainerid, icasuser.title as title, icasuser.action as action
FROM icasuser, logon_info
WHERE (SYSDATE - TO_DATE(TRIM(icasuser.edate), 'YYYY-MM-DD')) > 90 AND icasuser.userid NOT IN (SELECT logon_info.sbcuid FROM logon_info GROUP BY logon_info.sbcuid)
)Ò»£ºÏÈÓÐÓÅ»¯µÚÒ»¸öunion×Ӿ䣺
--ÓÃʱ0.6s
select count(*) from (SELECT
m.userid AS userid, m.rsa AS rsa, m.roleid AS roleid,
m.fname as fname, m.lname as lname, m.centerid as centerid,
m.status as status, m.priv as priv, m.password as password,
m.edate as edate, m.coachid as coachid, m.analystid as analystid,
m.trainerid as trainerid, m.title as title, m.action as action
from icasuser m where exists(select 'x' from ( SELECT t.userid as usid FROM icasuser t, logon_info d
where t.userid=d.sbcuid group by t.userid having max(d.login_time) < sysdate-90 ) gg where usid=m.userid))
¼ÌÐøÓÅ»¯°Ñexists»»Îªin£º
--ÓÃʱ0.04s£º
select count(*) from(SELECT
m.userid AS userid, m.rsa AS rsa, m.roleid AS roleid,
m.fname as fname, m.lname as lname, m.centerid as centerid,
m.status as status, m.priv as priv, m.password as password,
m.edate as edate, m.coachid as coachid, m.analystid as analystid,
m.trainerid as trainerid, m.title as title, m.action as action
from icasuser m where userid in(select usid from ( SELECT t.userid as usid FROM icasuser t, logon_info d
where t.userid=d.sbcuid group by t.userid having max(d.login_time) < sysdate-90 )))
¶þ:¸ÄдԭÓï¾ä:
ÓÃʱ0.8s
select count(*) from (
SELECT
m.userid AS userid, m.rsa AS rsa, m.roleid AS roleid,
m.fname as fname, m.lname as lname, m.centerid as centerid,
m.status as status, m.priv as priv, m.password as password,
m.edate as edate, m.coachid as coachid, m.analystid as analystid,
m.trainerid as trainerid, m.title as title, m.action as action
from icasuser m where userid in(select usid from ( SELECT t.userid as usid FROM icasuser t, logon_info d
where t.userid=d.sbcuid group by t.userid having max(d.login_time) < sysdate-90 )
) or (SYSDATE - TO_DATE(TRIM(m.edate), 'YYYY-MM-DD')) > 90)
¼ÌÐø:
°Ñor¸ÄΪunion:
--ÓÃʱ0.047s select count(*) from(SELECT
m.userid AS userid, m.rsa AS rsa, m.roleid AS roleid,
m.fname as fname, m.lname as lname, m.centerid as centerid,
m.status as status, m.priv as priv, m.password as password,
m.edate as edate, m.coachid as coachid, m.analystid as analystid,
m.trainerid as trainerid, m.title as title, m.action as action
from icasuser m where userid in(select usid from ( SELECT t.userid as usid FROM icasuser t, logon_info d
where t.userid=d.sbcuid group by t.userid having max(d.login_time) < sysdate-90 ))
union
SELECT
m.userid AS userid, m.rsa AS rsa, m.roleid AS roleid,
m.fname as fname, m.lname as lname, m.centerid as centerid,
m.status as status, m.priv as priv, m.password as password,
m.edate as edate, m.coachid as coachid, m.analystid as analystid,
m.trainerid as trainerid, m.title as title, m.action as action
from icasuser m where (SYSDATE - TO_DATE(TRIM(m.edate), 'YYYY-MM-DD')) > 90)