有如下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_idgroup 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.