SQL Server ÖйØÓÚEXCEPTºÍINTERSECTµÄÓ÷¨
ÊìÁ·Ê¹ÓÃSQL ServerÖеĸ÷ÖÖÓ÷¨»á¸ø²éѯ´øÀ´ºÜ¶à·½±ã¡£½ñÌì¾Í½éÉÜÒ»ÏÂEXCEPTºÍINTERSECT¡£×¢Òâ´ËÓï·¨½öÔÚSQL Server 2005¼°ÒÔÉϰ汾֧³Ö¡£
EXCEPTÊÇÖ¸ÔÚµÚÒ»¸ö¼¯ºÏÖдæÔÚ£¬µ«ÊDz»´æÔÚÓÚµÚ¶þ¸ö¼¯ºÏÖеÄÊý¾Ý¡£
INTERSECTÊÇÖ¸ÔÚÁ½¸ö¼¯ºÏÖж¼´æÔÚµÄÊý¾Ý¡£
²âÊÔÈçÏ£º
create table t1(id int,mark char(2))
go
create table t2(id int,mark char(2))
go
insert into t1
select 1,'t1' union all
select 2,'t2' union all
select 3,'t3' union all
select 4,'t4'
go
insert into t2
select 2,'t2' union all
select 3,'m3' union all
select 5,'m5' union all
select 6,'t6'
go
select * from t1
EXCEPT
select * from t2
go
select * from t1
INTERSECT
select * from t2
go
--EXCEPT½á¹û¼¯Îª
--1 t1
--3 t3
--4 t4
--INTERSECT½á¹û¼¯Îª
--2 t2
EXCEPTºÍINTERSECTµÄÓÅÏȼ¶£º
ΪÁ˲âÊÔËüÃÇÖ®¼äµÄÓÅÏȼ¶£¬ÔËÐÐÏÂÃæµÄ²âÊÔ´úÂë:
create table t3(int id,mark char(2))
go
insert into t3
select 3,'t3' union all
select 3,'r3' union all
select 5,'m5' union all
select 5,'r5' union all
select 7,'b7' union all
select 8,'b8'
go
select * from t1
EXCEPT
select * from t2
INTERSECT
select * from t3
--ÔËÐнá¹û
--1 t1
--2 t2
--3 t3
--4 t4
Ϊʲô»á³öÏÖÈçÉϽá¹ûÄØ£¬Çë¿´ÏÂÃæµÄÖ´Ðмƻ®£º
ÔÀ´t2ºÍt3ÏȽøÐеÄINTERSECTÔËË㣬µÃ³ö5 m5½á¹û¼¯£¬ÔÙºÍt1½øÐÐEXCEPTÔËËã¡£
ÈçÐè×ªÔØ£¬Çë×¢Ã÷±¾ÎÄÔ´´×ÔCSDN TJVictorרÀ¸£ºhttp://blog.csdn.net/tjvictor
Ïà¹ØÎĵµ£º
Ò»£®¼òµ¥SQL²éѯ£º
1£©:ͳ¼ÆÃ¿¸ö²¿ÃÅÔ±¹¤µÄÊýÄ¿
select dept,count(*) from employee group by dept;
2£©:ͳ¼ÆÃ¿¸ö²¿ÃÅÔ±¹¤µÄÊýÄ¿´óÓÚÒ»¸öµÄ¼Ç¼
select dept,count(*) from employee group by dept having count(*)>1;
3£©:ͳ¼Æ¹¤×ʳ¬¹ý1200µÄÔ±¹¤ËùÔÚ²¿ÃŵÄÃû³Æ
select e.first_name,salary,d.name
from s_emp ......
ÔÚSQL ServerµÄÐÔÄܵ÷ÓÅÖУ¬ÓÐÒ»¸ö²»¿É±ÈÄâµÄÎÊÌ⣺ÄǾÍÊÇÈçºÎÔÚÒ»¶ÎÐèÒª³¤Ê±¼äµÄ´úÂë»ò±»Æµ·±µ÷ÓõĴúÂëÖд¦ÀíÁÙʱÊý¾Ý¼¯?±í±äÁ¿ºÍÁÙʱ±íÊÇÁ½ÖÖÑ¡Ôñ¡£
¡¡¡¡ÔÚSQL ServerµÄÐÔÄܵ÷ÓÅÖУ¬ÓÐÒ»¸ö²»¿É±ÈÄâµÄÎÊÌ⣺ÄǾÍÊÇÈçºÎÔÚÒ»¶ÎÐèÒª³¤Ê±¼äµÄ´úÂë»ò±»Æµ·±µ÷ÓõĴúÂëÖд¦ÀíÁÙʱÊý¾Ý¼¯?±í±äÁ¿ºÍÁÙʱ±íÊÇÁ½ÖÖÑ¡Ôñ¡£¼ÇµÃÔÚ¸øÒ»¼Ò ......
ÓÐÁ½ÖÖ·½·¨¶¯Ì¬ÖرàÒë´æ´¢¹ý³Ì£º 1.ÔÚCreateʱ¼ÓÉÏRECOMPILEÑ¡Ïî CREATE PROCEDURE dbo.PersonAge (@MinAge INT, @MaxAge INT)
WITH RECOMPILE
AS
SELECT *
from dbo.tblTable 2.ÔÚÖ´ÐÐʱ¼ÓÉÏRECOMPILEÑ¡Ïî EXEC dbo.PersonAge 65,70 WITH RECOMPILE ²»ÍƼöʹÓõڶþÖÖ·½·¨£¬ÓÈÆäÔÚÉú²ú»·¾³ ......
ÔÒò·ÖÎö:
SERVERPROPERTY º¯ÊýµÄ ServerName ÊôÐÔÓë@@SERVERNAME ·µ»ØÏàËÆµÄÐÅÏ¢¡£ServerName ÊôÐÔÌṩWindows ·þÎñÆ÷ºÍʵÀýÃû³Æ£¬Á½Õß¹²Í¬¹¹³ÉΨһµÄ·þÎñÆ÷ʵÀý£»@@SERVERNAME Ìṩµ±Ç°ÅäÖõı¾µØ·þÎñÆ÷Ãû³Æ¡£
Èç¹û°²×°Ê±Î´¸ü¸ÄĬÈÏ·þÎñÆ÷Ãû³Æ£¬Ôò ServerName ÊôÐÔºÍ @@SERVERNAME ·µ»ØÏàͬµÄÐÅÏ¢¡£Èç¹ûÔÚ°²×°Ê±Òѽ«±¾µ ......