第一个语句
select name from org_member where id not in (select member_id from logon_log)第二个语句select
a.id,c.name,d.name,b.name,a.name,a.org_department_id,a.org_level_id,a.org_account_id,a.org_post_id,
b.path,b.org_account_id,substr(b.path,0,(instr(b.path,'.',-1)-1))
from
org_member a left join org_department b on a.org_department_id=b.id
left join org_account c on c.id = a.org_account_id
left join org_department d on d.path = substr(b.path,0,(instr(b.path,'.',-1)-1))
and d.org_account_id = c.id
where a.id not in (select member_id from logon_log)
order by c.sort_id,b.sort_id,a.sort_id第一个语句是最基础的查询,第二个语句是为了取得更多的相关信息作了详细的处理,但二个语句,我觉得的我写的总体意思是一样的,为什么二个语句执行出来结果不一样?第二个语句的结果会多呢?
也即是说org_member表的org_department_id列与org_department 表的id列不是一对一
而是一对多的关系
其他两个表类似
所以最后结果会多~!
你应该检查你表org_member,看下我的列子就能明白了SQL> with t1 as (select 1 id,'aa' name from dual
2 union all select 2 id,'bb' name from dual)
3 select * from t1 where t1.name='aa'; ID NA
---------- --
1 aaSQL> with t1 as (select 1 id,'aa' name from dual
2 union all select 2 id,'bb' name from dual),
3 t2 as (select 1 id,'第一个' address from dual
4 union all select 1 id,'第二个' address from dual )
5 select * from t1 left join t2 on t1.id=t2.id
6 where t1.name='aa'; ID NA ID ADDRES
---------- -- ---------- ------
1 aa 1 第一个
1 aa 1 第二个SQL>
a.id,c.name,d.name,b.name,a.name,a.org_department_id,a.org_level_id,a.org_account_id,a.org_post_id,
b.path,b.org_account_id,substr(b.path,0,(instr(b.path,'.',-1)-1))
from
org_member a ,org_department b , org_account c ,org_department d
where a.id not in (select member_id from logon_log) and a.org_department_id=b.id
and and c.id = a.org_account_id and d.path = substr(b.path,0,(instr(b.path,'.',-1)-1)) and d.org_account_id = c.id
order by c.sort_id,b.sort_id,a.sort_id