代码C7-联接与APPLY运算符.doc

第7章 联接和APPLY运算符 7.1 联接的基本知识 7.1.3 列名限定和选择列表的使用 例如，下面的语句为表分配了别名，并且用表的别名对列加以限定，从而提高了语句的可读性： SELECT D.DepName, E.EmpID, E.EmpName FROM Departs AS D JOIN Employees AS E ON (D.DepID = E.DepID) 例如，在下面的语句中虽然E.EmpID列没有包含在SELECT列表中，但是仍旧通过该列进行数据筛选。 SELECT D.DepName, E.EmpName FROM Departs AS D JOIN Employees AS E ON (D.DepID = E.DepID) WHERE E.EmpID = 1 7.2.2 使用交叉联接查询全部数据 首先执行下面的语句创建两个示例表：Employees和Orders，表中内容分别如表7-6和表7-7所示。 CREATE TABLE Employees (EmpID int NOT NULL, EmpName char(10) NOT NULL); CREATE TABLE Orders ( EmpID int NOT NULL, SeasonNbr char(10) NOT NULL, Sales money DEFAULT 0.00 NOT NULL); INSERT INTO Employees VALUES (1, Grace), (2, Ken), (3, Tom); INSERT INTO Orders VALUES (1, Season 1, 100.00), (1, Season 2, 100.00), (2, Season 3, 120.00), (2, Season 4, 130.00); Employees中存放着雇员信息，Orders存放着雇员的季度销售数据。现在假设要返回雇员的每季度的销售数据。这个问题看起来似乎非常简单，但是请考虑一下，如果从Employees表到Orders表进行了仅执行像下面这样的标准内部联接（INNER JOIN），则只会获得雇员有销售数据的季度，如表7-8所示。 SELECT Employees.EmpName, Orders.SeasonNbr, Orders.Sales FROM Employees INNER JOIN Orders ON Employees.EmpID = Orders.EmpID; 可以使用交叉联接的方式解决此问题。创建一个名为Seasons的辅助表，表中存放着4个季度名称。 CREATE TABLE Seasons (SeasonNbr char(10)); INSERT INTO Seasons VALUES (Season 1), (Season 2), (Season 3), (Season 4); 对Employees表和Seasons表进行交叉联接，会得到如表7-9所示的查询结果。 SELECT * FROM Employees CROSS JOIN Seasons; 可以看到，交叉联接后的结果中每个雇员都具有了4个季度。然后再将该结果与Orders表进行左外联接，可以得到最终要求的数据格式。下面是完整查询语句，查询结果如表7-10所示。 SELECT Employees.EmpName, Seasons.SeasonNbr, CASE WHEN Orders.Sales IS NULL THEN 0 ELSE Orders.Sales END AS SeasonSales FROM Employees CROSS JOIN Seasons -- 交叉联接 LEFT OUTER JOIN Orders -- 再将交叉联接结果与Orders进行左外联结 ON Employees.EmpID = Orders.EmpID AND Seasons.SeasonNbr = Orders.SeasonNbr; 7.2.3 使用交叉联接优化查询性能 参考下面的语句： SELECT EmpID, SeasonNbr, Sales, CAST(Sales / (SELECT SUM(Sales) FROM Orders) * 100 AS decimal(5,2)) AS Per, Sales - (SELECT AVG(Sales) FROM Orders) AS Diff FROM O