--想从下面表中查询出对应的某个省有多少人,传入的条件是省份,查询出的人数必须是ISEnable=1的,要用join语句的
tab1
id province IsEnable
1 '北京' true
2 '上海' true
3 '重庆' true
4 '广州' truetab2
id city IsEnable pid
1 '朝阳' true 1
2 '浦东' true 2
3 '重庆' false 3
4 '广州' true 4tab3
id name cid IsEnable
1 'zhangsan' 1 true
2 'lisi' 1 true
3 'wangwu' 3 true
4 'zhaoliu' 2 true
from tab1 a inner join tab2 b on a.id=b.pid
inner join tab3 c on b.id=c.cid
where c.IsEnable='true'
from tab1 a inner join tab2 b on a.id=b.pid
inner join tab3 c on b.id=c.cid
where c.IsEnable='true' and a.id=@id--参数
group by a.id,a.province
WHERE C.province= @yourpara AND A.isEnable=1
pid province IsEnable
1 '北京' true
2 '上海' true
3 '重庆' true
4 '广州' truetab2
cid city IsEnable pid
1 '朝阳' true 1
2 '浦东' true 2
3 '重庆' false 3
4 '广州' true 4tab3
id cid uname
1 1 'zhangsan'
2 1 'lisi'
3 3 'wangwu'
4 2 'zhaoliu'tab4
uid uname IsEnable
1 'zhangsan' true
2 'lisi' true
3 'wangwu' true
4 'zhaoliu' true
a.id,a.province,人数=count(1)
from
tab1 a inner join tab2 b on a.id=b.pid
join
tab3 c on b.id=c.cid
where
c.IsEnable='true'
from tab1 a inner join tab2 b on a.id=b.pid
inner join tab3 c on b.id=c.cid
inner join tab4 d on c.id=d.uid
where c.IsEnable='true' and a.id=@id--参数
group by a.id,a.province
亲手测试的sql语句
where t3.id in (select t4.uid from tab4 t4 where t4.IsEnable = 1)
and t3.cid in (select t2.cid from tab2 t2 where t2.pid in (select t1.pid from tab1 t1 where t1.province = '北京'));
其实本人认为子查询更好点
select a.id,a.province,人数=count(*)
from tab1 a inner join tab2 b on a.id=b.pid
inner join tab3 c on b.id=c.cid
inner join tab4 d on c.id=d.uid
where c.IsEnable='true' and b.IsEnable='true'
group by a.id,a.province