--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.
第四章 子查询(1)
最新推荐文章于 2012-09-06 18:00:43 发布