SELECT 语句收藏(2000 & 2005)

使用 SQL-92 CROSS JOIN 语法

以下示例返回 Employee 和 Department 这两个表的叉积。所返回的列表包含 EmployeeID 行和所有 Department 名称行的所有可能的组合。

USE AdventureWorks ;

GO

SELECT

e.EmployeeID, d.Name AS Department

FROM

HumanResources.Employee e

CROSS JOIN

HumanResources.Department d

ORDER BY

e.EmployeeID, d.Name ;

使用 SQL-92 FULL OUTER JOIN 语法

以下示例返回产品名称以及 SalesOrderDetail 表中任何对应的销售订单。该示例还将返回在 Product 表中没有列出产品的任何销售订单，以及销售订单不同于在 Product 表中列出的销售订单的任何产品。

USE AdventureWorks ;

GO

-- The OUTER keyword following the FULL keyword is optional.

SELECT

    p.Name, sod.SalesOrderID

FROM

    Production.Product p

FULL OUTER JOIN

    Sales.SalesOrderDetail sod

ON

    p.ProductID = sod.ProductID

WHERE

    p.ProductID IS NULL

OR

    sod.ProductID IS NULL

ORDER BY

    p.Name ;

使用 SQL-92 LEFT OUTER JOIN 语法

以下示例基于 ProductID 联接两个表，并保留左表中不匹配的行。Product 表与每个表中的 ProductID 列上的 SalesOrderDetail 表相匹配。所有产品，无论是否已订购，都将在结果集中显示。

SELECT

    p.Name, sod.SalesOrderID

FROM

    Production.Product p

LEFT OUTER JOIN

    Sales.SalesOrderDetail sod

ON

    p.ProductID = sod.ProductID

ORDER BY

    p.Name ;

使用 SQL-92 INNER JOIN 语法

以下示例返回所有产品名称和销售订单 ID。

USE AdventureWorks ;

GO

-- By default, SQL Server performs an INNER JOIN if only the JOIN

-- keyword is specified.

SELECT

    p.Name, sod.SalesOrderID

FROM

    Production.Product p

INNER JOIN

    Sales.SalesOrderDetail sod

ON

    p.ProductID = sod.ProductID

ORDER BY p.Name ;

使用 SQL-92 RIGHT OUTER JOIN 语法

以下示例基于 TerritoryID 联接两个表，并保留右表中不匹配的行。SalesTerritory 表与每个表中的 TerritoryID 列上的 SalesPerson 表相匹配。不论是否分配了地区，所有业务员均在结果集中显示，。

USE AdventureWorks ;

GO

SELECT

    st.Name AS Territory, sp.SalesPersonID

FROM

    Sales.SalesTerritory st

RIGHT OUTER JOIN

    Sales.SalesPerson sp

ON

    st.TerritoryID = sp.TerritoryID ;

使用 HASH 和 MERGE 联接提示

以下示例在 Product、ProductVendor 和 Vendor 表之间执行三表联接，生成产品及其供应商的列表。查询优化器使用 MERGE 联接来联接 Product 和 ProductVendor（p 和 pv）。然后，Product 和 ProductVendor MERGE 联接（p 和 pv）的结果被 HASH 联接到 Vendor 表，生成（p 和 pv）和 v。

 

重要事项：

指定联接提示后，要执行 INNER JOIN 时，INNER 关键字不再为可选，而必须显式说明。

SELECT

    p.Name AS ProductName, v.Name AS VendorName

FROM

    Production.Product p

INNER MERGE JOIN

    Purchasing.ProductVendor pv

ON

    p.ProductID = pv.ProductID

INNER HASH JOIN

    Purchasing.Vendor v

ON

    pv.VendorID = v.VendorID

ORDER BY

    p.Name, v.Name ;

关于哈希连接：哈希联接有两种输入：生成输入和探测输入。查询优化器指派这些角色，使两个输入中较小的那个作为生成输入。

    以下介绍了不同类型的哈希联接：内存中的哈希联接、Grace 哈希联接和递归哈希联接。

  1. 内存中的哈希联接：哈希联接先扫描或计算整个生成输入，然后在内存中生成哈希表。根据计算得出的哈希键的哈希值，将每行插入哈希存储桶。如果整个生成输入小于可用内存，则可以将所有行都插入哈希表中。生成阶段之后是探测阶段。一次一行地对整个探测输入进行扫描或计算，并为每个探测行计算哈希键的值，扫描相应的哈希存储桶并生成匹配项。
  2. Grace 哈希联接：如果生成输入大于内存，哈希联接将分为几步进行。这称为"Grace 哈希联接"。每一步都分为生成阶段和探测阶段。首先，消耗整个生成和探测输入并将其分区（使用哈希键上的哈希函数）为多个文件。对哈希键使用哈希函数可以保证任意两个联接记录一定位于相同的文件对中。因此，联接两个大输入的任务简化为相同任务的多个较小的实例。然后将哈希联接应用于每对分区文件。
  3. 递归哈希联接：如果生成输入非常大，以至于标准外部合并的输入需要多个合并级别，则需要多个分区步骤和多个分区级别。如果只有某些分区较大，则只需对那些分区使用附加的分区步骤。为了使所有分区步骤尽可能快，将使用大的异步 I/O 操作以便单个线程就能使多个磁盘驱动器繁忙工作。

注意： 如果生成输入仅稍大于可用内存，则内存中的哈希联接和 Grace 哈希联接的元素将结合在一个步骤中，生成混合哈希联接。

  1. 在优化过程中不能始终确定使用哪种哈希联接。因此，SQL Server 开始时使用内存中的哈希联接，然后根据生成输入的大小逐渐转换到 Grace 哈希联接和递归哈希联接。
  2. 如果优化器错误地预计两个输入中哪个较小并由此确定哪个作为生成输入，生成角色和探测角色将动态反转。哈希联接确保使用较小的溢出文件作为生成输入。这一技术称为"角色反转"。至少一个文件溢出到磁盘后，哈希联接中才会发生角色反转。

注意： 角色反转的发生独立于任何查询提示或结构。角色反转不会显示在查询计划中；角色反转对于用户是透明的。

  1. 哈希援助：术语"哈希援助"有时用于描述 Grace 哈希联接或递归哈希联接。

    注意： 递归哈希联接或哈希援助会导致服务器性能降低。如果跟踪中显示许多哈希警告事件，请更新正在联接的列上的统计信息。

使用 TABLESAMPLE 从表中的行样本读取数据

以下示例在 FROM 子句中使用 TABLESAMPLE，返回 AdventureWorks 数据库中的 Customer 表中所有行的 10%，或者1000行数据。

SELECT

    *

FROM

    Sales.Customer

TABLESAMPLE SYSTEM (10 PERCENT) ;

--SELECT

--    *

--FROM

--    Sales.Customer

--TABLESAMPLE SYSTEM (1000 ROWS) ;

注意：不能在视图或内联表值函数的定义中指定 TABLESAMPLE。

对于TABLESAMPLE一些说明：

TABLESAMPLE SYSTEM 返回的行数为总行数的一个近似百分比值。它为表中物理大小为 8 KB 的每一页生成一个随机值。根据某一页的随机值和查询中指定的百分比决定是否将该页包含在样本中。样本中包含的每一页都返回样本结果集中的所有行。例如，如果指定 TABLESAMPLE SYSTEM 10 PERCENT，SQL Server 将返回占指定表数据页约 10% 的所有行。如果行平均分布到表中的页上，而且表中有足够的页数，则返回的行数应接近请求的样本大小。但是，由于为每一页生成的随机值独立于为任何其他页生成的值，因此有可能返回比请求的百分比更大或更小的百分比页数。可以使用 TOP(n) 运算符将行数限制在给定最大值以内。

如果指定了行数，而不是基于表中总行数的百分比，行数将会转换为行的百分比以及应返回的页的百分比。然后对计算出的百分比执行 TABLESAMPLE。

如果表只包含一页，将返回该页上的所有行或不返回任何一行。即使页上存在 100 行，TABLESAMPLE SYSTEM 也只返回 100% 或 0% 的行。因此，对于包含少量页的表（不包括 text、ntext 或 image 数据），不建议使用 SYSTEM。若要使 TABLESAMPLE SYSTEM 更接近较小的表的指定样本大小，请考虑可使表的行分散到多页中的选项。对于带有聚集索引的表，可以使用 FILLFACTOR 并重新组织聚集索引，以减少页上的行数。在其他表上，可以通过在表定义中添加空的 char(4000) 或 char(2000) 列来增加行大小，使每一页仅能容纳一行或两行。

使用 APPLY

以下示例假定数据库中存在具有如下架构的以下表：

  • Departments: DeptID, DivisionID, DeptName, DeptMgrID
  • EmpMgr: MgrID, EmpID
  • Employees: EmpID, EmpLastName, EmpFirstName, EmpSalary

还有一个表值函数（GetReports(MgrID)）可以返回指定的 MgrID 直接或间接领导的所有员工的列表（EmpID、EmpLastName、EmpSalary）。

该示例使用 APPLY 返回所有部门和部门中的所有员工。如果某个部门没有任何员工，则不返回该部门的任何行。

SELECT

    DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary

FROM

    Departments d

CROSS APPLY

    dbo.GetReports(d.DeptMgrID) ;

关于APPLY的说明：

使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中的列集，后跟右输入返回的列的列表。

APPLY 有两种形式： CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY 既返回生成结果集的行，也返回不生成结果集的行，其中表值函数生成的列中的值为 NULL。

MSDN的例子：

--Create Employees table and insert values

CREATE TABLE Employees

(

empid int NOT NULL,

mgrid int NULL,

empname varchar(25) NOT NULL,

salary money NOT NULL,

CONSTRAINT PK_Employees PRIMARY KEY(empid),

)

GO

INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)

INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)

INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)

INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)

INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)

INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)

INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)

INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)

INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)

INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)

INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)

INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)

INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)

INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)

GO

--Create Departments table and insert values

CREATE TABLE Departments

(

deptid INT NOT NULL PRIMARY KEY,

deptname VARCHAR(25) NOT NULL,

deptmgrid INT NULL REFERENCES Employees

)

GO

INSERT INTO Departments VALUES(1, 'HR', 2)

INSERT INTO Departments VALUES(2, 'Marketing', 7)

INSERT INTO Departments VALUES(3, 'Finance', 8)

INSERT INTO Departments VALUES(4, 'R&D', 9)

INSERT INTO Departments VALUES(5, 'Training', 4)

INSERT INTO Departments VALUES(6, 'Gardening', NULL)

Departments 表中的多数部门都具有一个经理 ID，这些 ID 与 Employees 表中的雇员相对应。以下表值函数接受雇员 ID 作为参数，并返回该雇员和他/她的所有下属。

GO

CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS @TREE TABLE

(

empid INT NOT NULL,

empname VARCHAR(25) NOT NULL,

mgrid INT NULL,

lvl INT NOT NULL

)

AS

BEGIN

WITH Employees_Subtree(empid, empname, mgrid, lvl)

AS

(

-- Anchor Member (AM)

SELECT empid, empname, mgrid, 0

FROM employees

WHERE empid = @empid

 

UNION all

 

-- Recursive Member (RM)

SELECT e.empid, e.empname, e.mgrid, es.lvl+1

FROM employees AS e

JOIN employees_subtree AS es

ON e.mgrid = es.empid

)

INSERT INTO @TREE

SELECT * FROM Employees_Subtree

 

RETURN

END

GO

这个函数运用了是递归的方法，Employees_Subtree表作用一个中间临时表，先接受了UNION all上面的查询，返回的结果保存在 Employees_Subtrez中，然后再与employees表进行连接查询，查出下属员工的有哪些，最后再把结果保存到 Employees_Subtrez中一直执行（AM）语句直到查询返回空结束。

使用 PIVOT 和 UNPIVOT

以下示例返回按员工 ID（164、198、223、231 和 233）下达，并按供应商 ID 分类的采购订单的数量。

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5

FROM

(

    -- 不要包含多余的列

SELECT PurchaseOrderID, EmployeeID, VendorID

    FROM Purchasing.PurchaseOrderHeader

) p

PIVOT

(

    COUNT (PurchaseOrderID)

    FOR EmployeeID IN

( [164], [198], [223], [231], [233] )

) AS pvt

ORDER BY VendorID;

结果部分为：

表格 1

VendorID

Emp1

Emp2

Emp3

Emp4

Emp5

1

4

3

5

4

4

2

4

1

5

5

5

3

4

3

5

4

4

4

4

2

5

5

4

5

5

1

5

5

5

7

5

2

5

5

5

8

4

2

5

4

5

10

4

1

5

4

5

 

若要对表进行逆透视，请假定在上一个示例中生成的结果集是作为 pvt 存储的。该查询如下所示。

--Create the table and insert values as portrayed in the previous example.

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,

Emp3 int, Emp4 int, Emp5 int)

GO

INSERT INTO pvt VALUES (1,4,3,5,4,4)

INSERT INTO pvt VALUES (2,4,1,5,5,5)

INSERT INTO pvt VALUES (3,4,3,5,4,4)

INSERT INTO pvt VALUES (4,4,2,5,5,4)

INSERT INTO pvt VALUES (5,5,1,5,5,5)

GO

--Unpivot the table.

SELECT VendorID, Employee, Orders

FROM

(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) p

UNPIVOT

(

Orders FOR Employee IN

(Emp1, Emp2, Emp3, Emp4, Emp5)

)AS unpvt

GO

结果部分为：

表格 2

VendorID

Employee

Orders

1

Emp1

4

1

Emp2

3

1

Emp3

5

1

Emp4

4

1

Emp5

4

2

Emp1

4

2

Emp2

1

2

Emp3

5

2

Emp4

5

 

PIVOT原理我大概是这样理解，首先按值列之外的列（ EmployeeID, VendorID）对输入表Purchasing.PurchaseOrderHeader进行分组汇总，类似执行下面的语句：

SELECT

    VendorID,EmployeeID,COUNT(PurchaseOrderID) AS [Total]

FROM

    Purchasing.PurchaseOrderHeader

WHERE

    EmployeeID IN (164, 198, 223, 231, 233)

GROUP BY

    VendorID,EmployeeID

ORDER BY

    VendorID

结果部分为：

表格 3

VendorID

EmployeeID

Total

1

164

4

1

198

3

1

223

5

1

231

4

1

233

4

2

164

4

2

198

1

2

223

5

2

231

5

2

233

5

3

164

4

3

198

3

3

223

5

3

231

4

3

233

4

4

164

4

4

198

2

4

223

5

4

231

5

PIVOT根据FOR EmployeeID IN指定的值164, 198, 223, 231, 233，首先在结果集中建立名为164, 198, 223, 231, 233的列，然后从[表格3]所示的中间结果中取出Total列中取出相符合的值，分别放置到164, 198, 223, 231, 233的列中。此时得到的结果集的别名为pvt（见语句中AS pvt的指定）。结果集的内容如[表格1]所示。

最后根据

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5 FROM

的指定，从别名pvt结果集中检索数据，并分别将名为164, 198, 223, 231, 233的列在最终结果集中重新命名为Emp1、Emp2、Emp3、Emp4、Emp5。这里需要注意的是FROM的含义，其表示从经PIVOT关系运算符得到的pvt结果集中检索数据，而不是从Purchasing.PurchaseOrderHeader中检索数据。

UNPIVOT原理我大概是这样理解，UNPIVOT与PIVOT执行几乎完全相反的操作，将列转换为行。但是，UNPIVOT并不完全是PIVOT的逆操作，由于在执行PIVOT过程中，数据已经被进行了分组汇总，所以使用UNPIVOT并不会重现原始表值表达式的结果。

上述的UNPIVOT例子，大概按下面的步骤来执行：

  1. 首先建立一个临时结果集的结构，该结构中包Pvt表中除IN (Emp1, Emp2, Emp3, Emp4, Emp5)之外的列，以及Orders FOR Employee中指定的值列（Orders）和透视列（Employee）。
  2. 将在Pvt中逐行检索数据，将表的列名称（在IN (Emp1, Emp2, Emp3, Emp4, Emp5)中指定）放入Orders列中，将相应的值放入到Employee列中。最后得到的结果集所[表格2]示。

posted on 2007-03-05 00:55  Sherrys  阅读(689)  评论(0)  编辑  收藏  举报

导航