資源描述：
《sqlserver中關(guān)于except和intersect的用法》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在工程資料-天天文庫。
1、SQLServer中關(guān)于EXCEPT和INTERSECT的用法熟練使用SQLServer中的各種用法會(huì)給查詢帶來很多方便。今天就介紹一下EXCEPT和INTERSECT。注意此語法僅在SQLServer2005及以上版本支持。EXCEPT是指在第一個(gè)集合中存在，但是不存在于第二個(gè)集合中的數(shù)據(jù)。INTERSECT是指在兩個(gè)集合中都存在的數(shù)據(jù)。測(cè)試如下：[c-sharp]viewplaincopyprint?1.create?table?t1(id?int,mark?char(2))??2.go??3.create?table?t2(id?int,mark?char(2))??4.go??5
2、.insert?into?t1??6.????select?1,'t1'?union?all??7.????select?2,'t2'?union?all??8.????select?3,'t3'?union?all??9.????select?4,'t4'??10.go??11.insert?into?t2??12.????select?2,'t2'?union?all??13.????select?3,'m3'?union?all??14.????select?5,'m5'?union?all??15.????select?6,'t6'??16.go??17.select?*?fro
3、m?t1??18.EXCEPT??19.select?*?from?t2??20.go??21.select?*?from?t1??22.INTERSECT??1.select?*?from?t2??2.go??3.??4.--EXCEPT結(jié)果集為??5.--1?t1??6.--3?t3??7.--4?t4??8.??9.--INTERSECT結(jié)果集為??10.--2?t2????EXCEPT和INTERSECT的優(yōu)先級(jí)：為了測(cè)試它們之間的優(yōu)先級(jí)，運(yùn)行下面的測(cè)試代碼:[c-sharp]viewplaincopyprint?1.create?table?t3(int?id,mark?cha
4、r(2))??2.go??3.insert?into?t3??4.????select?3,'t3'?union?all??5.????select?3,'r3'?union?all??6.????select?5,'m5'?union?all??1.????select?5,'r5'?union?all??2.????select?7,'b7'?union?all??3.????select?8,'b8'??4.go??5.select?*?from?t1??6.EXCEPT??7.select?*?from?t2??8.INTERSECT??9.select?*?from?t3??1
5、0.??11.--運(yùn)行結(jié)果??12.--1?t1??13.--2?t2??14.--3?t3??15.--4?t4???為什么會(huì)出現(xiàn)如上結(jié)果呢，請(qǐng)看下面的執(zhí)行計(jì)劃：?原來t2和t3先進(jìn)行的INTERSECT運(yùn)算，得出5?m5結(jié)果集，再和t1進(jìn)行EXCEPT運(yùn)算。?如需轉(zhuǎn)載，請(qǐng)注明本文原創(chuàng)自CSDNTJVictor專欄：http://blog.csdn.net/tjvictor
