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
解决方案 »
- delphi连接oracle时用system和sys以外的用户名和密码总是报错 怎么弄啊??
- 求助:oracle 启动监听问题
- 使用PL\SQL更新数据库中的日期提示确实右括号缺失
- 如何执行字段中的sql语句?
- 统计表中相同地区(区号)的数量,并倒序排序.
- 用java程序往oracle数据库中插入大量的数据,大概有一亿六千万条,如何能提高效率
- ORACLE中如何把表导成.DMP文件
- 学习中问索引的语法
- !!!!!!!!!!连接的语句使用索引的问题
- "已用时间: 00: 10:651.251"是设么意思?
- 大批量导入数据出现的问题,请指教哦!
- oracle10提示监听程序当前无法识别连接描述符中所给出的sid,解决问题的重谢!
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)