荣耀彩票代理

IT技术互动交流平台

SQL Server聚焦APPLY运算符(二十七)

作者:JeffckyWang  来源:IT165收集  发布日期:2016-12-19 20:32:17

前言

荣耀彩票代理QISHIYOUXIEXINDETEXINGZAISQL ServerZAOJIUYIJINGCHUXIANGUO,DANSHIRUOFEIXITONGDEQUXUEXISHUJUKUNIHUIFAXIANZAISHIJIXIANGMUZHONGBIERENDESQLQISHISHIBIJIAOFUZADE,QISHILIYONGXINDESQL ServerYUFAHUIGENGJIAFANGBIANHEJIANJIE,CONGBENJIEKAISHIWOMENJIANGJIANGSHUYIXIESQL ServerZHONGZAOYICHUXIANDEXINYUFA,JIANDUANDENEIRONG,SHENRUDELIJIE,Always to reivew the basics。

初探APPLY运算符

荣耀彩票代理APPLYYUNSUANFUSHIYIGEFEICHANGQIANGDADEBIAOYUNSUANFU,DANSHIAPPLYBUSHIBIAOZHUNDE,XIANGDUIYINGDEBIAOZHUNJIAOZUOLATERAL,DANSHICIBIAOZHUNBINGWEIZAISQL ServerZHONGSHIXIAN。XIANGSUOYOUBIAOYUNSUANFUYIYANG,GAIYUNSUANFUYONGYUCHAXUNDEFROMZIJUZHONG。APPLYYUNSUANFUZHICHIDELEIXINGSHICROSS APPLYHEOUTER APPLY。CROSS APPYJINJINSHISHIYIGELUOJICHAXUNCHULIJIEDUAN,EROUTER APPLYSHISHILELIANGGEJIEDUAN,APPLYYUNSUANFUDUILIANGGESHURUBIAOJINXINGCAOZUO,DIERGEKEYISHIYIGEBIAOBIAODASHI,WOMENJIANGAPPLYLIANGCEDEBIAOFENBIEJIAOZUOZUOCEBIAOHEYOUCEBIAO,YOUCEBIAOTONGCHANGSHIYIGEPAISHENGBIAOHUOTVF(NEIQIANBIAOZHIHANSHU)。CROSS APPLYYUNSUANFUSHISHIYIGELUOJICHAXUNCHULIJIEDUAN-TAJIANGYOUCEDEBIAOBIAODASHIYINGYONGDAOZUOCEBIAODEMEIYIXING,BINGSHENGCHENGYIGEZUHEJIEGUOJIDEJIEGUOBIAO。CROSS APPLYlLEISIYUJIAOCHALIANJIEZHONGDECROSS JOIN,DANSHISHIYONGCROSS APPLYYUNSUANFU,YOUCEDEBIAOBIAODASHIKEYIDUILAIZIZUOCEBIAODEMEIYIXINGBIAOSHIYIGEBUTONGDEXINGJI,ZHEISHIYULIANJIEDEBUTONGZHICHU。DANGZAIYOUCESHIYONGYIGEPAISHENGBIAO,BINGQIEPAISHENGBIAOCHAXUNZHONGYINYONGLAIZIZUOCEBIAODESHUXING,JIUKEYISHIXIANCIMUBIAO,HUOZHESHIZAIYOUCESHIYONGYIGENEIQIANTVF,KEYICHUANDIZUOCEDESHUXINGZUOWEISHURUCANSHU,TONGYANGKEYISHIXIANCIMUDE-ZHAICHAOZISQL Server 2012JICHUJIAOCHENG。XIAMIANWOMENKANYIGEJIANDANDELIZI。

USE TSQL2012
GO

SELECT C.custid, A.orderid, A.orderdate
FROM Sales.Customers AS C
    CROSS APPLY
        (SELECT TOP(3) orderid, empid, orderdate, requireddate 
        FROM Sales.Orders AS O
        WHERE O.custid = C.custid
        ORDER BY orderdate DESC, orderid DESC) AS A;

荣耀彩票代理SHANGSHUWANCHENGDESHIFANHUIMEIGEKEHUZUIJINDE3GEDINGDAN。WOMENKEYIJIANGYOUCEDEBIAOBIAODASHIKANZUOSHIYIGEXIANGGUANZICHAXUN,YOUCEDEBIAOBIAODASHITONGGUOYINYONGcustidDUILAIZICustomersBIAODEMEIYIXINGJINXINGCHULIBINGFANHUIMEIGEKEHUDEZUIJINDE3GEDINGDAN,SHIBUSHIKANQILAIHENQINGSHUANGNI,XIAMIANWOMENJIANGJINYIBUTANTAOAPPLYYUNSUANFUDEZUOYONG。

进一步探讨APPLY运算符

SHANGMIANWOMENKANDAOTONGGUOXIANGGUANZICHAXUNLAIJINXINGCHAXUNXIANDEDAIMAYOUDIANCHOULOU,WOMENZAILAIKANYIGELIZI。CHAXUNMEIGEDANJIAZUIGAODEDINGDAN,WOMENTONGGUOZICHAXUNLAISHIXIAN。

CROSS APPLY

USE AdventureWorks2012
GO

SELECT 
     SalesOrderID
    ,OrderDate
    ,MaxUnitPrice =(SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID)
FROM Sales.SalesOrderHeader AS soh

荣耀彩票代理RUSHANGCAOZUOKANSIDAIMABIJIAOJIANJIEYENENGWANCHENGWOMENDECHAXUNSUQIU,DANSHIWOMENYONGPAISHENGBIAOLAIJINXINGCHAXUNYOUSHIZENYANGDENI? 

USE AdventureWorks2012
GO

SELECT 
    soh.SalesOrderID
    ,soh.OrderDate
    ,sod.max_unit_price
FROM Sales.SalesOrderHeader AS soh
JOIN
(
    SELECT 
        max_unit_price = MAX(sod.UnitPrice),
        SalesOrderID
    FROM Sales.SalesOrderDetail AS sod
    GROUP BY sod.SalesOrderID
) sod
ON sod.SalesOrderID = soh.SalesOrderID

CISHIYOUYULIANGGEBIAOWANQUANBUXIANGGUAN,WOMENXUYAOTONGGUOGROUP BYWANCHENGZAIJINXINGJOIN,DAIMABUSHIXIANDEFEICHANGYONGZHONGMA,ZHEIHAISHIJIANDANDE,DANGYOUDUOGEBIAOSHIJIUBIJIAOFUZALE,DAOZHIDAIMAJIUBUZAIJUYOUKEDUXING。DANSHIZICONGZAISQL Server 2005ZHONGYOULEAPPLYMAMAZAIYEBUYONGDANXINWODUBUDONGFUZADEDAIMALE,WOMENKANKANCROSS APPLYSHIZENYANGSHIXIANDE。

USE AdventureWorks2012
GO

SELECT 
    soh.SalesOrderID
    ,soh.OrderDate
    ,sod.max_unit_price
FROM Sales.SalesOrderHeader AS soh
CROSS APPLY
(
    SELECT 
        max_unit_price = MAX(sod.UnitPrice)
    FROM Sales.SalesOrderDetail AS sod
    WHERE soh.SalesOrderID = sod.SalesOrderID
) sod

DANGWOMENLIYONGNEIBULIANJIESHICISHIJOINZHONGDECHAXUNSHIDULIDESUOYIXUYAOJINXINGGROUP BY,ERDUIYUCROSS APPLYTABENSHENJIUSHIDUILAIZIZUOCEDEBIAOZHONGMEIYIXINGJIUXINGCHULIBINGFANHUI,TONGSHILIYONGCROSS APPLYTAYECHAOYUELEXIANGGUANZICHAXUN,BIRUSHUOWOMENHAIXUYAOCHACHUMEIGEDINGDANDEZONGJIANI,WOMENLIYONGXIANGGUANZICHAXUNXUYAOZAICIQIANRUSELECTZIJU。

SELECT 
     SalesOrderID           
    ,OrderDate              
    ,MaxUnitPrice           = (SELECT MAX(sod.UnitPrice) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID)
    ,SumLineTotal           = (SELECT SUM(LineTotal) FROM Sales.SalesOrderDetail sod WHERE soh.SalesOrderID = sod.SalesOrderID)
FROM Sales.SalesOrderHeader AS soh

ERLIYONGCROSS APPLYZHIXUTIANJIAJIHEHANSHUSUMJIKE

USE AdventureWorks2012
GO

SELECT 
    soh.SalesOrderID
    ,soh.OrderDate
    ,sod.max_unit_price
    ,sod.sum_line_total
FROM Sales.SalesOrderHeader AS soh
CROSS APPLY
(
    SELECT 
        max_unit_price = MAX(sod.UnitPrice)
        ,sum_line_total = SUM(sod.LineTotal)
    FROM Sales.SalesOrderDetail AS sod
    WHERE soh.SalesOrderID = sod.SalesOrderID
) sod 

OUTER APPLY

DUIYUOUTER APPLY,RUGUOYOUCEDEBIAOBIAODASHIFANHUIYIGEKONGJIHE,CROSS APPLYYUNSUANFUBUHUIFANHUIXIANGYINGDEZUOCEXING,YEJIUSHISHUOOUTER APPLYHEZAIPAISHENGBIAOSHANGJINXINGLEFT JOINSHIDENGTONGDE,RUXIA:

SELECT 
    soh.SalesOrderID
    ,soh.OrderDate
    ,sod.max_unit_price
FROM Sales.SalesOrderHeader AS soh
LEFT JOIN
(
    SELECT 
        max_unit_price = MAX(sod.UnitPrice),
        SalesOrderID
    FROM Sales.SalesOrderDetail AS sod
    GROUP BY sod.SalesOrderID
) sod
ON sod.SalesOrderID = soh.SalesOrderID

荣耀彩票代理CISHIWOMENLIYONGOUTER APPLYZESHIRUXIA:

USE AdventureWorks2012
GO

SELECT 
    soh.SalesOrderID
    ,soh.OrderDate
    ,sod.max_unit_price
FROM Sales.SalesOrderHeader AS soh
OUTER APPLY
(
    SELECT 
        max_unit_price = MAX(sod.UnitPrice)
    FROM Sales.SalesOrderDetail AS sod
    WHERE soh.SalesOrderID = sod.SalesOrderID
) sod

SHANGSHUDUIYUAPPLYYOUCEBIAOBIAODASHISHIYIGEPAISHENGBIAO,CISHIWEILEFENGZHUANG,WOMENKEYISHIYONGTVFNEIQIANBIAOZHIHANSHULAISHIXIAN。QISHIJIANGNEIQIANBIAOZHIHANSHULAIDAITIPAISHENGBIAOSHIXIANMEIGEKEHUZUIJINDE3GEDINGDAN。SHOUXIANWOMENFENGZHUANGYIGEBIAOZHIHANSHU

USE TSQL2012
GO

IF OBJECT_ID('dbo.TopOrders') IS NOT NULL
    DROP FUNCTION dbo.TopOrders;
GO

CREATE FUNCTION dbo.TopOrders
    (@custid  AS INT, @n  AS  INT)
    RETURNS TABLE
AS RETURN

    SELECT  orderid, empid, orderdate, requireddate
    FROM Sales.Orders
    WHERE  custid = @custid
    ORDER BY orderdate DESC, orderid DESC
    OFFSET 0 ROWS FETCH FIRST @n ROWS ONLY;
GO

JIEZHELIYONGCROSS APPLYJINXINGCHAXUN。

USE TSQL2012
GO

SELECT C.custid, C.companyname, A.orderid, A.empid, A.requireddate
FROM Sales.Customers AS C
 CROSS APPLY dbo.TopOrders(C.custid, 3) AS A;

SHANGMIANWOMENTONGGUOFENGZHUANGNEIQIANBIAOZHIHANSHUDAITIPAISHENGBIAOSHIDAIMAGENGJUKEDUXINGHEKEWEIHUXING。DAOCIWOMENKEYIDECHUYIDIANJIBENJIELUN。

荣耀彩票代理APPLYYUNSUANFUSHIYONGFENXIJIELUN:DANGXUYAODUIBIAOZHONGDEMEIYIXINGJINXINGYINGYONGSHI,QIEXUYAOJIANGSUOYOUJIEGUOJIZUHEDAOYIGEJIEGUOJIBIAOZHONGSHI,CISHIWOMENYINGGAISHIYONGAPPLYYUNSUANFU,ZHIYUSHISHIYONGCROSS APPLYHAISHIOUTER APPLYGENJUCHANGJINGERDING,SUIRANAPPLYYOUCEBIAOKEYIYONGXIANGGUANZICHAXUNHUOZHEPAISHENGBIAOLAISHIXIAN,DANSHISHIDEDAIMAYONGZHONGHEKEWEIHUXINGCHA,TONGGUOFENGZHUANGNEIQIANBIAOZHIHANSHULAISHIXIANKEYISHUOSHIDUIYOUCEBIAOTONGGUOXIANGGUANZICHAXUNHUOZHEPAISHENGBIAOLAISHIXIANDEWANMEITIDAIZHE。

总结

BENJIEWOMENJIANGJIELEAPPLYYUNSUANFUZHONGLIANGZHONGLEIXINGDESHIYONG,XIAYIJIEWOMENLAIFENXIXIAGUANYUCROSS APPLY VS INNER JOINDEXINGNENGWENTI,TONGSHIYESHUOMINGXIACROSS APPLYHEOUTER APPLYDEYINGYONGCHANGJING。JIANDUANDENEIRONG,SHENRUDELIJIE,WOMENXIAJIEZAIHUI。

Tag标签:   
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规