Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

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


Ïà¹ØÎĵµ£º

oracle sqlÃæÊÔÌâ2

Ò»£®¼òµ¥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²éѯѡÔñ±íÖдÓ10µ½15µÄ¼Ç¼

      ORDER BY ×Ӿ䰴һÁлò¶àÁУ¨×î¶à 8,060 ¸ö×Ö½Ú£©¶Ô²éѯ½á¹û½øÐÐÅÅÐò¡£ÓÐ¹Ø ORDER BY ×Ó¾ä×î´ó´óСµÄÏêϸÐÅÏ¢£¬Çë²ÎÔÄ ORDER BY ×Ó¾ä (Transact-SQL)¡£
      Microsoft SQL Server 2005 ÔÊÐíÔÚ from ×Ó¾äÖÐÖ¸¶¨¶Ô SELECT ÁбíÖÐδָ¶¨µÄ±íÖеÄÁнøÐÐÅÅÐò¡£ORDE ......

SQLº¯Êý´óÈ«2

--
¾ÛºÏº¯Êý
use
pubs
go
select

avg
(
distinct
price)  
--
Ëãƽ¾ùÊý
from
titles
where
type
=
'
business
'
go

use
pubs
go
select

max
(ytd_sales)  
--
×î´óÊý
from
titles
go

use
pubs
go
select

min
(ytd_sales)
--
×îСÊý
fr ......

SQL Server ÓÅ»¯´æ´¢¹ý³ÌµÄÆßÖÖ·½·¨

ÓÅ»¯´æ´¢¹ý³ÌÓкܶàÖÖ·½·¨£¬ÏÂÃæ½éÉÜ×î³£ÓõÄ7ÖÖ¡£
1.ʹÓÃSET NOCOUNT ONÑ¡Ïî
ÎÒÃÇʹÓÃSELECTÓï¾äʱ£¬³ýÁË·µ»Ø¶ÔÓ¦µÄ½á¹û¼¯Í⣬»¹»á·µ»ØÏàÓ¦µÄÓ°ÏìÐÐÊý¡£Ê¹ÓÃSET NOCOUNT ONºó£¬³ýÁËÊý¾Ý¼¯¾Í²»»á·µ»Ø¶îÍâµÄÐÅÏ¢ÁË£¬¼õСÍøÂçÁ÷Á¿¡£
2.ʹÓÃÈ·¶¨µÄSchema
ÔÚʹÓÃ±í£¬´æ´¢¹ý³Ì£¬º¯ÊýµÈµÈʱ£¬×îºÃ¼ÓÉÏÈ·¶¨µÄSchema¡£ÕâÑù¿ÉÒÔÊ ......

SQLʱ¼ä±È½Ï dateDiff·½·¨Ê¹ÓÃ

DateDiff
¡¡¡¡DateDiff: SQL serverº¯Êý
¡¡¡¡·µ»Ø Variant (Long) µÄÖµ£¬±íʾÁ½¸öÖ¸¶¨ÈÕÆÚ¼äµÄʱ¼ä¼ä¸ôÊýÄ¿¡£
¡¡¡¡Óï·¨
¡¡¡¡DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
¡¡¡¡DateDiff º¯ÊýÓï·¨ÖÐÓÐÏÂÁÐÃüÃû²ÎÊý£º
¡¡¡¡²¿·Ö ÃèÊö
¡¡¡¡interval ±ØÒª¡£×Ö·û´®±í´ïʽ£¬±íʾÓÃÀ´¼ÆËãdate1 ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ