select b.UserName,c.UserCode,nvl(c.LogNum,0) lognum from PUser b,(select a.UserCode,count(decode(a.AccessMsg,'登录系统','x',null)) LogNum from UserLog a group by a.UserCode) c where c.UserCode=b.UserCode(+)
select a.usercode, a.userName from puser a where a.usercode not in (select b.usercode from userlog b where a.usercode = b.usercode); 如果数据量不是很大,你就可以这么写,如果数据量很大,还是把not in换掉吧。
select b.UserName,b.UserCode,nvl(c.LogNum,0) lognum from PUser b,(select a.UserCode,count(decode(a.AccessMsg,'登录系统','x',null)) LogNum from UserLog a group by a.UserCode) c where c.UserCode=b.UserCode(+)这样试试
select a.username,a.usercode,b.lognum from PUser a, ( select c.usercode,count(1) as lognum from PUser c,Userlog d where c.usercode=d.usercode group by c.usercode ) b where a.usercode=b.usercode union all select username,usercode,0 from PUser where usercode not in (select usercode from Userlog)
试试先连接后统计: select b.UserName,b.UserCode,count(decode(a.AccessMsg,'登录系统','x',null)) from PUser b,LogNum a where c.UserCode=b.UserCode(+) group by b.UserName,b.UserCode
去掉union all使用left outer jion select a.username,a.usercode,nvl(b.lognum,0) from PUser a left outer join ( select c.usercode,count(1) as lognum from PUser c,Userlog d where c.usercode=d.usercode group by c.usercode ) b on a.usercode=b.usercode
或者使用(+)的寫法select a.username,a.usercode,nvl(b.lognum,0) from PUser a, ( select c.usercode,count(1) as lognum from PUser c,Userlog d where c.usercode=d.usercode group by c.usercode ) b where a.usercode=b.usercode(+)
select a.usercode,username,num from pUser a, ( select usercode,sum(num) num from ( select usercode,1 num from UserLog union all select usercode,0 from pUser ) group by usercode )b where a.usercode=b.usercode
前面确实错了,没看清你的表 select b.UserName,b.UserCode,nvl(c.LogNum,0) lognum from PUser b,(select a.UserCode,count(decode(a.AccessMsg,'登录系统','x',null)) LogNum from UserLog a group by a.UserCode) c where c.UserCode(+)=b.UserCode
如果数据量不是很大,你就可以这么写,如果数据量很大,还是把not in换掉吧。
from PUser a,
(
select c.usercode,count(1) as lognum
from PUser c,Userlog d
where c.usercode=d.usercode
group by c.usercode
) b
where a.usercode=b.usercode
union all
select username,usercode,0
from PUser
where usercode not in (select usercode from Userlog)
---------- -------- ----------
user1 001 3
user2 002 3
user3 003 0
3 rows selected
select b.UserName,b.UserCode,count(decode(a.AccessMsg,'登录系统','x',null))
from PUser b,LogNum a
where c.UserCode=b.UserCode(+) group by b.UserName,b.UserCode
select a.username,a.usercode,nvl(b.lognum,0)
from PUser a left outer join
(
select c.usercode,count(1) as lognum
from PUser c,Userlog d
where c.usercode=d.usercode
group by c.usercode
) b
on a.usercode=b.usercode
from PUser a,
(
select c.usercode,count(1) as lognum
from PUser c,Userlog d
where c.usercode=d.usercode
group by c.usercode
) b
where a.usercode=b.usercode(+)
(
select usercode,sum(num) num from
(
select usercode,1 num from UserLog
union all
select usercode,0 from pUser
)
group by usercode
)b
where a.usercode=b.usercode
select b.UserName,b.UserCode,nvl(c.LogNum,0) lognum from PUser b,(select a.UserCode,count(decode(a.AccessMsg,'登录系统','x',null)) LogNum from UserLog a group by a.UserCode) c where c.UserCode(+)=b.UserCode
ORARichard(没钱的日子......) ,用你的方法很好,谢谢。