SQL学习笔记14——子查询

【子查询】

①把一个查询结果作为一个表来使用，就是子查询

select T.* from (select userId from UserInfo where userage >50 ) as Twhere T.UserId<5

小括号的作用就是把查询结果作为一个表，同时通过关键字as给该表起了个别名T

②把一个查询结果作为一个表达式使用，就是子查询

select * kfrom UserInfo where UserAge < (select avg(UserAge) from UserInfo)

案例：

--查询 顾客表中  公司员工超过2个员工的  顾客信息select * from SalesLT.Customer where CompanyName in (select CompanyName from SalesLT.Customer group by CompanyName having count(1) >2)

--查询员工最多公司的员工信息select * from SalesLT.Customerwhere CompanyName=(select top 1 companyName as 员工数 from SalesLT.Customer group by CompanyName  order by count(1) desc)

--select * from  SalesLT.SalesOrderHeader--where CustomerID in--(--select CustomerID from SalesLT.Customer--where CompanyName=(select top 1 companyname from SalesLT.Customer--group by CompanyName  order by count(1) desc)--)--use [0413db]--select * from AreaFull   --取山东省的地级市： 县级市----山东省的id--select AreaId from AreaFull where AreaName=N'山东省'----山东的地级市查询出来了--select * from AreaFull where AreaPid =(--select AreaId from AreaFull where AreaName=N'山东省'--)----把所有山东省的 所有县 查询出来--select * from AreaFull where AreaPid in(--select AreaId from AreaFull where AreaPid =(--select AreaId from AreaFull where AreaName=N'山东省'--)--) and AreaName like '%县'