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