22,210
社区成员
发帖
与我相关
我的任务
分享
--想从下面表中查询出对应的某个省有多少人,传入的条件是省份,查询出的人数必须是ISEnable=1的,要用join语句的
tab1
id province IsEnable
1 '北京' true
2 '上海' true
3 '重庆' true
4 '广州' true
tab2
id city IsEnable pid
1 '朝阳' true 1
2 '浦东' true 2
3 '重庆' false 3
4 '广州' true 4
tab3
id name cid IsEnable
1 'zhangsan' 1 true
2 'lisi' 1 true
3 'wangwu' 3 true
4 'zhaoliu' 2 true
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
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 a.id=@id--参数
group by a.id,a.province
select
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'
tab1
pid province IsEnable
1 '北京' true
2 '上海' true
3 '重庆' true
4 '广州' true
tab2
cid city IsEnable pid
1 '朝阳' true 1
2 '浦东' true 2
3 '重庆' false 3
4 '广州' true 4
tab3
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
SELECT * FROM tab3 A LEFT JOIN tab2 B ON A.cid=B.id LEFT JOIN tab1 C ON B.pid=C.id
WHERE C.province= @yourpara AND A.isEnable=1
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
where c.IsEnable='true' and a.id=@id--参数
group by a.id,a.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
where c.IsEnable='true'