代码C7联接与APPLY运算符.docx
- 文档编号:30570304
- 上传时间:2023-08-16
- 格式:DOCX
- 页数:26
- 大小:28.02KB
代码C7联接与APPLY运算符.docx
《代码C7联接与APPLY运算符.docx》由会员分享,可在线阅读,更多相关《代码C7联接与APPLY运算符.docx(26页珍藏版)》请在冰豆网上搜索。
代码C7联接与APPLY运算符
第7章联接和APPLY运算符
7.1联接的基本知识
7.1.3列名限定和选择列表的使用
例如,下面的语句为表分配了别名,并且用表的别名对列加以限定,从而提高了语句的可读性:
SELECTD.DepName,E.EmpID,E.EmpName
FROMDepartsASDJOINEmployeesASE
ON(D.DepID=E.DepID)
例如,在下面的语句中虽然E.EmpID列没有包含在SELECT列表中,但是仍旧通过该列进行数据筛选。
SELECTD.DepName,E.EmpName
FROMDepartsASDJOINEmployeesASE
ON(D.DepID=E.DepID)
WHEREE.EmpID=1
7.2.2使用交叉联接查询全部数据
首先执行下面的语句创建两个示例表:
Employees和Orders,表中内容分别如表7-6和表7-7所示。
CREATETABLEEmployees
(EmpIDintNOTNULL,
EmpNamechar(10)NOTNULL);
CREATETABLEOrders
(EmpIDintNOTNULL,
SeasonNbrchar(10)NOTNULL,
SalesmoneyDEFAULT0.00NOTNULL);
INSERTINTOEmployees
VALUES(1,'Grace'),
(2,'Ken'),
(3,'Tom');
INSERTINTOOrders
VALUES(1,'Season1',100.00),
(1,'Season2',100.00),
(2,'Season3',120.00),
(2,'Season4',130.00);
Employees中存放着雇员信息,Orders存放着雇员的季度销售数据。
现在假设要返回雇员的每季度的销售数据。
这个问题看起来似乎非常简单,但是请考虑一下,如果从Employees表到Orders表进行了仅执行像下面这样的标准内部联接(INNERJOIN),则只会获得雇员有销售数据的季度,如表7-8所示。
SELECTEmployees.EmpName,Orders.SeasonNbr,Orders.Sales
FROMEmployeesINNERJOINOrders
ONEmployees.EmpID=Orders.EmpID;
可以使用交叉联接的方式解决此问题。
创建一个名为Seasons的辅助表,表中存放着4个季度名称。
CREATETABLESeasons(SeasonNbrchar(10));
INSERTINTOSeasonsVALUES('Season1'),('Season2'),('Season3'),('Season4');
对Employees表和Seasons表进行交叉联接,会得到如表7-9所示的查询结果。
SELECT*
FROMEmployeesCROSSJOINSeasons;
可以看到,交叉联接后的结果中每个雇员都具有了4个季度。
然后再将该结果与Orders表进行左外联接,可以得到最终要求的数据格式。
下面是完整查询语句,查询结果如表7-10所示。
SELECTEmployees.EmpName,Seasons.SeasonNbr,
CASE
WHENOrders.SalesISNULLTHEN0
ELSEOrders.Sales
ENDASSeasonSales
FROMEmployeesCROSSJOINSeasons--交叉联接
LEFTOUTERJOINOrders--再将交叉联接结果与Orders进行左外联结
ONEmployees.EmpID=Orders.EmpID
ANDSeasons.SeasonNbr=Orders.SeasonNbr;
7.2.3使用交叉联接优化查询性能
参考下面的语句:
SELECTEmpID,SeasonNbr,Sales,
CAST(Sales/(SELECTSUM(Sales)FROMOrders)*100ASdecimal(5,2))ASPer,
Sales-(SELECTAVG(Sales)FROMOrders)ASDiff
FROMOrders;
参考下面的语句,交叉联接后的结果如表7-12所示。
SELECT*
FROMOrders
CROSSJOIN(SELECTSUM(Sales)ASSumSales,
AVG(Sales)ASAvgSales
FROMOrders)ASOth;
从中可以看出,表扫描减少为2次。
SELECTEmpID,SeasonNbr,Sales,
CAST(Sales/SumSales*100ASdecimal(5,2))ASPer,
Sales-AvgSalesASDiff
FROMOrders
CROSSJOIN(SELECTSUM(Sales)ASSumSales,
AVG(Sales)ASAvgSales
FROMOrders)ASOth;
7.2.4为交叉联接添加WHERE子句
下面的语句指定仅将符合Employees.EmpID=Orders.EmpID条件的行才放入到结果集中。
查询结果见前面表7-8。
SELECTEmployees.EmpName,Orders.SeasonNbr,Orders.Sales
FROMEmployees
CROSSJOINOrders
WHEREEmployees.EmpID=Orders.EmpID;
该语句等同于下面的内部联接,并且会生成相同的执行计划。
SELECTEmployees.EmpName,Orders.SeasonNbr,Orders.Sales
FROMEmployees
INNERJOINOrders
ONEmployees.EmpID=Orders.EmpID;
7.3内部联接
7.3.2使用等值进行内部联接
使用下面的语句创建示例表和所需要的数据,SoftEmployees和SoftSkills的内容分别如表7-13和表7-14所示。
CREATETABLESoftEmployees
(EmpNamechar(10)NOTNULL,
SkillNamechar(20)NOTNULL);
CREATETABLESoftSkills
(SkillNamechar(20)NOTNULL);
INSERTINTOSoftEmployees
VALUES('Jones','SQLServer'),
('Jones','C#'),
('Jones','XML'),
('Grace','VB'),
('Grace','C#'),
('Eddie','VB'),
('Eddie','J#'),
('Celko','SQLServer'),
('Celko','C#'),
('Celko','XML'),
('Celko','J#');
INSERTINTOSoftSkills
VALUES('SQLServer'),
('C#'),
('XML');
我们知道,在等值内部联接的情况下,得到的是两个表的交集。
也就是说,将SoftEmployees和SoftSkills按“SoftEmployees.SkillName=SoftSkills.SkillName”条件联接,会得到SoftEmployees中所有掌握SQLServer、C#、XML任一技术的雇员清单,如表7-15所示。
SELECT*
FROMSoftEmployeesASE1
INNERJOINSoftSkillsASS1
ONE1.SkillName=S1.SkillName;
下面是完整的查询语句,它按EmpName列分类汇总,计算出同时掌握三项技术的雇员。
返回结果为Jones和Celko。
SELECTEmpName
FROMSoftEmployeesASE1
INNERJOINSoftSkillsASS1
ONE1.SkillName=S1.SkillName
GROUPBYE1.EmpName
HAVINGCOUNT(E1.EmpName)=3;
为了使语句更加通用一些,可以用子查询代替3。
参考下面的语句:
SELECTEmpName
FROMSoftEmployeesASE1
INNERJOINSoftSkillsASS1
ONE1.SkillName=S1.SkillName
GROUPBYE1.EmpName
HAVINGCOUNT(E1.EmpName)=(SELECTCOUNT(*)FROMSoftSkills);
7.3.3使用不等值进行内部联接
下面是将SoftEmployees和SoftSkills按不等值条件联接的语句,查询结果如表7-16所示。
SELECT*
FROMSoftEmployeesASE1
INNERJOINSoftSkillsASS1
ONE1.SkillName<>S1.SkillName
下面的语句就是一个通过AND关键字将等值和不等值条件组合在一起的查询。
它的返回结果为Celko。
SELECTEmpName
FROMSoftEmployeesASE1
INNERJOINSoftSkillsASS1
ONE1.SkillName=S1.SkillNameANDE1.EmpName<>'Jones'
GROUPBYE1.EmpName
HAVINGCOUNT(E1.EmpName)=(SELECTCOUNT(*)FROMSoftSkills);
参考下面的语句:
SELECTEmpName
FROMSoftEmployeesASE1
INNERJOINSoftSkillsASS1
ONE1.SkillName=S1.SkillName
WHEREE1.EmpName<>'Jones'
GROUPBYE1.EmpName
HAVINGCOUNT(E1.EmpName)=(SELECTCOUNT(*)FROMSoftSkills);
7.4外部联接
7.4.2使用左外部联接保留左表全部行
下面来看一个比较实际的问题。
首先执行下面的语句创建两个示例表:
Employees和Orders,表中内容分别如表7-17和表7-18所示。
CREATETABLEEmployees
(EmpIDintNOTNULL,
EmpNamechar(10)NOTNULL);
CREATETABLEOrders
(EmpIDintNOTNULL,
SeasonNbrchar(10)NOTNULL,
SalesmoneyDEFAULT0.00NOTNULL);
INSERTINTOEmployees
VALUES(1,'Grace'),
(2,'Ken'),
(3,'Tom');
INSERTINTOOrders
VALUES(1,'Season1',100.00),
(1,'Season2',100.00),
(1,'Season3',120.00),
(1,'Season4',130.00),
(2,'Season1',200.00),
(2,'Season2',300.00),
(2,'Season3',150.00);
下面的语句进行了4次左外部联接,联接顺序自上而下依次进行。
第一次是将Employees与Orders(O1)联接,得到第一季度的销售数据,如表7-20所示;第二次是将表7-20所示的联接结果再次与Orders(O2)联接,得到第二季度的销售数据,如表7-21所示。
依次类推,执行完4次左外部联接后,就得到了表7-19所示的数据。
SELECTE1.EmpName,
O1.SalesASSeason1,
O2.SalesASSeason2,
O3.SalesASSeason3,
O4.SalesASSeason4
FROMEmployeesASE1
LEFTOUTERJOINOrdersASO1
ONE1.EmpID=O1.EmpIDANDO1.SeasonNbr='Season1'
LEFTOUTERJOINOrdersASO2
ONE1.EmpID=O2.EmpIDANDO2.SeasonNbr='Season2'
LEFTOUTERJOINOrdersASO3
ONE1.EmpID=O3.EmpIDANDO3.SeasonNbr='Season3'
LEFTOUTERJOINOrdersASO4
ONE1.EmpID=O4.EmpIDANDO4.SeasonNbr='Season4';
下面的语句充分利用了CASE函数的,仅使用了一次左外部联接。
SELECTE1.EmpID,E1.EmpName,
MAX(CASE
WHENO1.SeasonNbr='Season1'THENO1.Sales
ELSENULL
END)ASSeason1,
MAX(CASE
WHENO1.SeasonNbr='Season2'THENO1.Sales
ELSENULL
END)ASSeason2,
MAX(CASE
WHENO1.SeasonNbr='Season3'THENO1.Sales
ELSENULL
END)ASSeason3,
MAX(CASE
WHENO1.SeasonNbr='Season4'THENO1.Sales
ELSENULL
END)ASSeason4
FROMEmployeesASE1
LEFTOUTERJOINOrdersASO1
ONE1.EmpID=O1.EmpID
GROUPBYE1.EmpID,E1.EmpName;
7.4.3使用右外部联接保留右表全部行
右外部联接的功能与左外部联接类似,大多数情况下,左外部联接完全可以取代右外部联接。
例如,下面的语句只是交换了一下7.4.2节左外部联接示例语句中Employees和Orders表的位置,实现了右外部联接。
二者的查询结果完全相同。
SELECTE1.EmpID,E1.EmpName,
MAX(CASE
WHENO1.SeasonNbr='Season1'THENO1.Sales
ELSENULL
END)ASSeason1,
MAX(CASE
WHENO1.SeasonNbr='Season2'THENO1.Sales
ELSENULL
END)ASSeason2,
MAX(CASE
WHENO1.SeasonNbr='Season3'THENO1.Sales
ELSENULL
END)ASSeason3,
MAX(CASE
WHENO1.SeasonNbr='Season4'THENO1.Sales
ELSENULL
END)ASSeason4
FROMOrdersASO1
RIGHTOUTERJOINEmployeesASE1
ONE1.EmpID=O1.EmpID
GROUPBYE1.EmpID,E1.EmpName;
7.5自联接
7.5.1使用不同列实现自联接
下面语句中的E1是为了获取MgrID信息,E2是为了获取与E1中MgrID相匹配的部门经理姓名。
查询结果如表7-23所示。
SELECTE1.EmpID,E1.EmpName,E1.MgrID,E2.EmpNameASMgrName
FROMEmployeesASE1
LEFTOUTERJOINEmployeesASE2
ONE1.MgrID=E2.EmpID;
7.5.2使用同一列实现自联接
下面的查询语句两次打开了Orders表,其中O1是为了获取雇员的销售额,O2是为了获取大于O1中当前雇员的销售额数据。
这实际上也是一个不等值联接查询。
查询结果如表7-25所示。
SELECTO1.EmpID,
MAX(O1.Sales)ASSales,
AVG(O2.Sales)ASAvgSales
FROMOrdersASO1
LEFTJOINOrdersASO2
ONO1.Sales GROUPBYO1.OrderID; 7.6多表联接 7.6.1顺序联接 下面的语句将创建4个示例表并向其中插入一些数据。 CREATETABLEProductions (ProductIDintNOTNULL, ProductNamechar(15)NOTNULL); CREATETABLESales (ProductIDintNOTNULL, ProductCntintNOTNULL); CREATETABLESpoliations (ProductIDintNOTNULL, ProductCntintNOTNULL); CREATETABLEStock (ProductIDintNOTNULL, ProductCntintNOTNULL); INSERTINTOProductions VALUES(1,'Bike-51'), (2,'Bike-52'), (3,'Bike-53'), (4,'Bike-54'); INSERTINTOSales VALUES(1,100), (2,120), (3,130); INSERTINTOSpoliations VALUES(1,10), (3,30); INSERTINTOStock VALUES(2,20), (3,10), (4,100); 参考下面的语句: SELECTP.ProductName, S.ProductCntASSaleCnt, Sp.ProductCntASSpoliatCnt, St.ProductCntASStockCnt FROMProductionsASP LEFTOUTERJOINSalesASS ONP.ProductID=S.ProductID LEFTOUTERJOINSpoliationsASSp ONP.ProductID=Sp.ProductID LEFTOUTERJOINStockASSt ONP.ProductID=St.ProductID; 7.6.2嵌套联接 下面的查询首先将Sales与Spoliations进行内部联接,获得了损坏比率,查询结果如表7-27所示。 SELECTS.ProductID, S.ProductCntASSaleCnt, Sp.ProductCntASSpCnt, CAST(1.*Sp.ProductCnt/S.ProductCntASdecimal(5,2))ASPer FROMSalesASS INNERJOINSpoliationsASSp ONS.ProductID=Sp.ProductID; 下一步应当将上面的结果与Productions进行外部联接,获得自行车名称信息。 下面是按照顺序执行方式编写的查询语句。 注意其中使用了右外部联接。 SELECTP.ProductName,S.ProductID, S.ProductCntASSaleCnt, Sp.ProductCntASSpCnt, CAST(1.*Sp.ProductCnt/S.ProductCntASdecimal(5,2))ASPer FROMSalesASS INNERJOINSpoliationsASSp ONS.ProductID=Sp.ProductID RIGHTOUTERJOINProductionsASP ONS.ProductID=P.ProductID; 下面的语句首先将里层的Sales与Spoliations进行联接,然后将联接结果再与外层的Productions执行左外部联接。 查询结果与表7-28所示完全相同。 SELECTP.ProductName,S.ProductID, S.ProductCntASSaleCnt, Sp.ProductCntASSpCnt, CAST(1.*Sp.ProductCnt/S.ProductCntASdecimal(5,2))ASPer FROMProductionsASP LEFTOUTERJOINSalesASS INNERJOINSpoliationsASSp ONS.ProductID=Sp.ProductID ONS.ProductID=P.ProductID; 此外,也可以使用圆括号来表达这种层次关系,表示先执行括号内的联接。 如: SELECTP.ProductName,S.ProductID, S.ProductCntASSaleCnt, Sp.ProductCntASSpCnt, CAST(1.*Sp.ProductCnt/S.ProductCntASdecimal(5,2))ASPer FROMProductionsASP LEFTOUTERJOIN(SalesASS INNERJOINSpoliationsASSp ONS.ProductID=S
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 代码 C7 联接 APPLY 运算