7、SQL基础整理（子查询）

（用来进行两个或以上表之间的查询）

1、首先新建一个bumen表和一个haha表，填充数据

2、利用两表进行子查询：

--部门人数大于5的部门中最大年龄的人的信息---select bumen as 部门,COUNT(*)as 人数 from haha group by bumen having COUNT(*)>5 select MAX(age) from haha where bumen = '销售部'select *from haha where bumen = '销售部' and age = 45---子查询select *from haha where age in (select MAX(age) from haha where bumen = '销售部')and bumen in (select bumen from haha group by bumen having COUNT(*)>5 )-------练习1:按年龄从小到大排序后第6、7、8人的信息select top 3 *from haha where code not in(select top 5 code from haha order by age)order by age--另一种select *from haha where code not in(select top 5 code from haha order by age)and code in(select top 8 code from haha order by age)-------练习2:查找男生里面年龄最大的人的信息select top 1 *from haha where sex in(select sex from haha where sex = '男') order by age-------练习3:查找人数最多的部门35岁的人的信息select *from haha where bumen in (select top 1 bumen from haha group by bumen order by  COUNT(*) desc)and age = 35--select MAX(COUNT(*))from haha order by bumen-----------分页--------------select top 5*from haha where code not in(select top 10 code from haha)----------一个表按照规定的条目(5)能分多少页(为防止自动默认为int型不能用5整型)select ceiling(COUNT(*)/5.0)from haha