Mysql子查询概念

select * from services ;// 服务
select * from clients ; //用户
select * from branches ; //分支机构
select * from branches_services ; //分支服务表
select bdes from branches , clients where clients.cid  = branches.cid and cilents.cname = 'songjian';内连接的方式
select bdesc from branches where cid = (select cid from clients where cname = 'songjian')子查询方式得到的

常见使用方式
select cid , count(*)
from branches group by cid having count(bid) = 2 //个数是两个的
select cname from clients where cid = 104;
合并
select cname from clients where cid = (select cid from branches group by cid having count(bid) = 2)
select bid from branches_service where bid = 1031 ;
select sname from service where sid = 2 ;
select sname from service where sid = 3;
select sname from service where sid = 4 ;
合并
select sname from service where sid in (select bid from branches_service where bid = 1031);
select * from clicnts where exists (select bid from branches_service group by bid having count(bid ) >= 4);
方式二
select bid count(sid) as total from branches_services group by bid ;
select avg(s.total) from (select bid ,count(sid) as total from branches_services group by bid ) as s ;
select cname from clients left join branches on clients.cid=branches.cid  where branches.bid is null ;
select cname from clients where cid = (
select clients.cid frombranches right join clients on cilents.cid = branches.cid where
branches.cid is null )

 

转载于:https://my.oschina.net/u/1169736/blog/525992