【子查询】
①把一个查询结果作为一个表来使用,就是子查询
select T.* from
(select userId from UserInfo where userage >50 ) as T
where 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.Customer
where 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 '%县'