日期:2014-05-17  浏览次数:20920 次

oracle left join 无法查出左表的全部数据,很疑惑
有如下sql语句
SQL code
    select st.name,count(si.id)
      from  t_school_type st  
 left join  t_school_info si on st.id = si.t_s_id
     where si.record_status = 1
  group by st.name;



t_school_type 有七条记录,
  id name
4c58f3f5-e38d-4954-9d38-c97d9e0b3331 普通初中
4c58f3f6-e37d-4984-9d38-c27d3e0b3332 职业初中
4c58f3f5-e38d-4954-9458-c27d360b3333 九年一贯制学校
4c58f3f5-e38d-4954-9458-c27d360b3334 普通小学
4c58f3f5-e38d-4954-9458-c27d360b3335 特殊教育学校
4c58f3f5-e38d-4954-9458-c27d360b3336 完全中学
4c58f3f5-e38d-4954-9458-c27d360b3337 普通高中

t_school_info 中t_s_id 是外键

为什么查询以后的结果只有六条,左表中的数据不能去不查询出来呢,疑惑

------解决方案--------------------
select st.name,count(si.id)
from t_school_type st,t_school_info si 
where st.id = si.t_s_id(+)
and si.record_status(+) = 1
group by st.name;
------解决方案--------------------
select st.name,count(si.id)
from t_school_type st
 left join t_school_info si on st.id = si.t_s_id
and si.record_status = 1
group by st.name;

把where换成and试试
------解决方案--------------------
SQL code
select st.name,count(si.id)
from  t_school_type st 
left join  (select * from t_school_info where record_status = 1)si on st.id = si.t_s_id
group by st.name;

------解决方案--------------------
select st.name,count(si.id)
from t_school_type st
 left join t_school_info si on st.id = si.t_s_id
where si.record_status = 1
group by st.name;
------------------------------
the result will be
STEP 1:
select * from t_school_type st left join t_school_info si on st.id = si.t_s_id;
this sql will find out every entry in table t_school_type;
we can name the result as "resulttable"

but after you add where condition
STEP 2:
select name,count(id) from resulttable where record_status=1 group by name;
the result will be filted.

So that's why you got the result like that, it's true.