【Microsoft SQL Server 2008 技术内幕：T-SQL语言基础】二、查询篇

sql2008 t-sql

单表查询

TOP选项
1. (结合order by)返回的是有固定顺序的游标
2. 可以使用percent关键字
3. 附加属性tiebreaker：
    在没有给定所有列的order by次序时，top语句的返回列可能是不确定的，sql server将按照物理顺序返回结果。
    tiebreaker是一个用以确定唯一结果顺序的field或field列表。
4. WITH TIES选项 (相对于tiebreaker)
    除了使用tiebreaker，还可以使用该选项返回具有所有相同结果的行。
    例如 select top(3) field1, field2, field3 from xtable，可能会返回a,b,c行或a,b,d行
    而select top(3) with ties field1, field2, field3 from xtable将返回所有的可能结果行: a,b,c,d

OVER子句
over子句为行定义一个窗口(window)，以进行窗口函数(window function, 又分为ranking和aggregate)计算。
    传统的聚合(aggregate)函数以group by查询作为操作上下文，每个每组仅返回一个值
    开窗聚合函数使用over子句为上下文，没有数据分组，可以在返回记录中同时使用from, where, group by中的可用字段。
    Ranking function: ROW_NUMBER, RANK, DENSE_RANK, NTITLE

select ROW_NUMBER() over(order by SalesOrderID) as RowNumber
	, RANK() over (order by SalesOrderID) as Rank  
	, SalesOrderID
	, ProductID
	, OrderQty
	, SUM(OrderQty) over(partition by SalesOrderID) as Total
from SalesLT.SalesOrderDetail as A

查询元数据

Sql server提供了一系列视图，用于获取数据库对象的元数据信息的工具：
目录视图 (Booksonline: catalog views): 提供数据库中各个对象的信息
信息架构视图 (Booksonline: INFORMATION_SCHEMA views): 基于ANSI SQL标准的形式提供元数据信息，不包含Sql server所特有的内容
系统存储过程和函数 (Booksonline: system stored procedures, system functions):

联接查询

1. 左联应用中非常有用的数字序列表，可以用来构造一个连续的结果集作为左联基础： 

set nocount on
if OBJECT_ID('dbo.Nums', 'U') is not null drop table dbo.Nums
create table dbo.Nums(n int not null primary key)

declare @i as int = 1  -- SQL 2008 新语法
begin tran
  while @i <=10000
  begin
    insert into dbo.Nums values(@i)
    set @i = @i +1
  end
commit tran
set nocount off

select DATEADD(day, n-1, '20060101') as orderdate
from dbo.Nums
where n <= DATEDIFF(day, '20060106', '20081231') + 1
order by orderdate

2. 左联的抵消

• 如果在左联的where子句中使用了非保留列的表达式作为条件，那么左联效果会被抵消，结果集会变成内联的效果。
• 因为该非保留列的表达式永远为: NULL<运算符><值>, 其值永远为unkown

3. 多表连接中使用外联容易出现的逻辑错误。下边是2.3点的例子

-- 1. 错误逻辑的例子
select C.CustomerID, O.SalesOrderID, OD.ProductID, OD.OrderQty
from SalesLT.Customer as C
	left join SalesLT.SalesOrderHeader as O
		on C.CustomerID = O.CustomerID
	join SalesLT.SalesOrderDetail as OD
		on O.SalesOrderID = OD.SalesOrderID	 -- 使用了非保留列作条件，将会抵消左联
		
-- 2. 正解1，连续左联
select C.CustomerID, O.SalesOrderID, OD.ProductID, OD.OrderQty
from SalesLT.Customer as C
	left join SalesLT.SalesOrderHeader as O
		on C.CustomerID = O.CustomerID
	left join SalesLT.SalesOrderDetail as OD -- 继续左联
		on O.SalesOrderID = OD.SalesOrderID	
		
-- 3. 正解2，先内联再左联
select C.CustomerID, O.SalesOrderID, OD.ProductID, OD.OrderQty
from SalesLT.SalesOrderHeader as O		
	join SalesLT.SalesOrderDetail as OD		 -- 先内联
		on O.SalesOrderID = OD.SalesOrderID			
	right join SalesLT.Customer as C         -- 再右联
		on O.CustomerID = C.CustomerID
		
-- 4. 正解3，类似正解2，使用括号，进行逻辑嵌套即可，代码省略

4. 表关联是的运算顺序为自左至右
5. 在外联查询中使用count应注意它会将外部行业纳入统计，因为它只根据行数进行统计

子查询

• 分为独立子查询(self-contained subquery)，相关子查询(correlated subquery，子查询中引用了外部查询的表)。
• 查询结果可以返回单值(标量 scalar)，多值(multi-valued)或表(table)

-- 返回前一条记录的子查询	
-- 注意，这里是最简单的一个例子，效率会比较低
-- 可以把条件转移到where子句以提高效率
-- 或者使用错位联接，效率应该更高	
select SalesOrderID, OrderDate
	, (select MAX(O2.SalesOrderID)
	   from SalesLT.SalesOrderHeader as O2
	   where O2.SalesOrderID < O1.SalesOrderID) as prevorderid
from SalesLT.SalesOrderHeader as O1

-- 连续聚合 (running aggregate)
-- 通常使用连续聚合时应该至少有一个时间这样类型的列来表达时间连续的概念
select orderyear, qty
	, (select SUM(O2.qty)
	   from SalesLT.OrderTotalByYear as O2
	   where O2.orderyear <= O1.orderyear) as runqty
from SalesLT.OrderTotalByYear as O1
order by orderyear

-- 行为不当的子查询1，返回没下过单的客户
-- 如果子查询的返回有null,则该查询结果永远为空，因为where子句中的条件结果为unknown
select CustomerID, CompanyName
from SalesLT.Customer as C
where CustomerID not in (select CustomerID 
						 from SalesLT.SalesOrderHeader) -- 没有考虑CustomerID为空的情况
-- 最佳实践： 1.必要时设置field为not null。2.时刻注意SQL的三值逻辑中null的处理


-- 行为不当的子查询2，相近名称造成的子查询混淆
-- 造成这个错误一方面由于名字相近
-- 另一方面是由于sql server的搜索策略：当内查询不存在指定的field时，继续搜索外部查询
-- 这种行为无意中将一个独立子查询转换成了相关子查询

create table SalesLT.MyShippers (
shipper_id int not null primary key,
companyname nvarchar(40) not null
)
create table SalesLT.Orders (
  custid int not null primary key,
  shipperid int not null
)

-- 返回把订单发货给43号客户的发货人
select shipper_id, companyname
from SalesLT.MyShippers
where shipper_id in ( select shipper_id     -- 注意这里不是 shipperid,
					  from SalesLT.Orders
					  where custid = 43)
					  
-- 最佳实践：
-- 1. 长远角度应该考虑统一、一致的命名方案
-- 2. 短期角度则应该在子查询的field前指定来源表