第四章 子查询(1)

--4.1 独立子查询
use TSQLFundamentals2008
go

declare @maxid as int = (select MAX(orderid) from Sales.Orders)

select orderid, orderdate, empid, custid
from Sales.Orders
where orderid = @maxid

--4.1.1 独立标量子查询
select orderid, orderdate, empid, custid
from Sales.Orders
where orderid = (select MAX(orderid) from Sales.Orders)

--4.1.2 独立多值子查询
select orderid 
from Sales.Orders
where empid in
(select e.empid 
from HR.Employees e 
where e.lastname like 'D%')

select custid, orderid, orderdate, empid
from Sales.Orders
where custid in 
(select c.custid 
from Sales.Customers as c
where c.country = N'USA');

--没有下过任何订单的客户
select custid, companyname
from Sales.Customers
where custid not in 
(select o.custid from Sales.Orders o)

use tempdb;
select *
into dbo.orders
from TSQLFundamentals2008.Sales.Orders
where orderid % 2=0

select n 
from dbo.Nums
where n between(select MAX(o.orderid) from dbo.orders o)
and (select MIN(o.orderid) from dbo.orders o)
and n not in(select o.orderid from dbo.orders o)

--4.2 相关子查询
--为每个客户返回其订单ID最大的订单
use TSQLFundamentals2008
select custid, orderid, orderdate, empid
from Sales.Orders as o1
where orderid =
(select MAX(o2.orderid) from Sales.Orders as o2
where o1.custid = o2.custid)

select custid, MAX(orderid) as maxorderid
from Sales.Orders
group by custid


--4.2.1 exists谓词
--与T-SQL中的大多数谓词不同, exists谓词使用的是二值逻辑,而不是三值逻辑
--返回下过订单的西班牙客户
select custid, companyname
from Sales.Customers as c
where country = N'Spain'
and exists
(select * from Sales.Orders as o
where c.custid = o.custid)

--返回没下过订单的西班牙客户
select custid, companyname
from Sales.Customers as c
where country = N'Spain'
and not exists
(select * from Sales.Orders as o
where c.custid = o.custid)

--4.3 高级子查询
--4.3.1返回前一个或后一个记录
--返回当前订单的信息和它的前一个订单的ID
select orderid, orderdate, empid, custid,
(select MAX(O2.orderid) from Sales.Orders o2
where o2.orderid < o1.orderid) as prevorderid
from Sales.Orders as o1

--返回当前订单的信息和它的下一个订单的ID
select orderid, orderdate, empid, custid,
(select MIN(o2.orderid) from Sales.Orders o2
where o2.orderid>o1.orderid) as nextorderid
from Sales.Orders as o1

--4.3.2 连续聚合
--返回每年的订单年份\订货量,以及连续几年的总订货量
select orderyear, qty, (select SUM(qty)
from Sales.OrderTotalsByYear o1
where o1.orderyear<=o2.orderyear) as totalqty
from Sales.OrderTotalsByYear o2
order by orderyear asc

--与IN不同的是,exists使用的是二值谓词逻辑,所以exists总是返回true或false,而绝不会返回unknown.