SQLServerÊý¾Ý¼¯ºÏµÄ½»¡¢²¢¡¢²î¼¯ÔËËã
SQLServer2005ͨ¹ýintersect,union,exceptºÍÈý¸ö¹Ø¼ü×Ö¶ÔÓ¦½»¡¢²¢¡¢²îÈýÖÖ¼¯ºÏÔËËã¡£
ËûÃǵĶÔÓ¦¹ØÏµ¿ÉÒԲο¼ÏÂÃæÍ¼Ê¾
Ïà¹Ø²âÊÔʵÀýÈçÏ£º
use tempdb
go
if (object_id ('t1' ) is not null ) drop table t1
if (object_id ('t2' ) is not null ) drop table t2
go
create table t1 (a int )
insert into t1 select 1 union select 2 union select 3
create table t2 (a int )
insert into t2 select 3 union select 4 union select 5
go
select * from t1 union select * from t2
go
/* Çó±í²¢¼¯
1
2
3
4
5*/
select * from t1 union all select * from t2
go
/*Çó±í²¢¼¯²»¹ýÂËÖØ¸´
1
2
3
3
4
5*/
select * from t1 except select * from t2
go
/*Çót1¶Ôt2µÄ²î¼¯
1
2*/
select * from t1 intersect select * from t2
go
/*Çót1¶Ôt2µÄ½»¼¯
3*/
±¾ÎÄÀ´×ÔCSDN²©¿Í£¬×ªÔØÇë±êÃ÷³ö´¦£ºhttp://blog.csdn.net/jinjazz/archive/2009/09/07/4527863.aspx
Ïà¹ØÎĵµ£º
¿ÉÒÔÓÃÀ©Õ¹´æ´¢¹ý³Ìxp_dirtree»ñÈ¡ÎļþÁÐ±í£¬ÓÃopenrowsetµ¹ÈëÊý¾Ýµ½¶þ½øÖÆ×ֶΡ£
openrowsetµÄÓ÷¨¿ÉÒԲο¼msdn
http://technet.microsoft.com/zh-cn/library/ms190312.aspx
Èç¹ûÎļþºÜ¶à£¬½¨Ò黹ÊÇÓóÌÐòµ¹ÈëÁË
if (object_id ('t_bulkResult' ) is not null )
drop table t_bulkResult ......
/*
sql xml ÈëÃÅ:
--by jinjazz
--http://blog.csdn.net/jinjazz
1¡¢xml: ÄÜÈÏÊ¶ÔªËØ¡¢ÊôÐÔºÍÖµ
2¡¢xpath: ѰַÓïÑÔ£¬ ......
ÔÚsqlserver(Ó¦¸Ã˵ÔÚĿǰËùÓÐÊý¾Ý¿â²úÆ·)Öд´½¨Ò»¸ö×ÊÔ´Èç±í£¬ÊÓͼ£¬´æ´¢¹ý³ÌÖж¼ÒªÅжÏÓë´´½¨µÄ×ÊÔ´ÊÇ·ñÒѾ´æÔÚ
ÔÚsqlserverÖÐÒ»°ã¿Éͨ¹ý²éѯsys.objectsϵͳ±íÀ´µÃÖª½á¹û£¬²»¹ý¿ÉÒÔÓиü·½±ãµÄ·½·¨
ÈçÏ£º
if object_id('tb_table') is not null
......
sp_databases --Áгö·þÎñÆ÷ÉϵÄËùÓÐÊý¾Ý¿â
sp_server_info --Áгö·þÎñÆ÷ÐÅÏ¢£¬Èç×Ö·û¼¯£¬°æ±¾ºÍÅÅÁÐ˳Ðò
sp_stored_procedures--Áгöµ±Ç°»·¾³ÖеÄËùÓд洢¹ý³Ì
sp_tables --Áгöµ±Ç°»·¾³ÖÐËùÓпÉÒÔ²éѯµÄ¶ÔÏó
sp_start_job --Á¢¼´Æô¶¯×Ô¶¯»¯ÈÎÎñ
sp_stop_job --Í£Ö¹ÕýÔÚÖ´ÐеÄ×Ô¶¯»¯ÈÎÎñ
sp_password --Ìí¼Ó»òÐ ......