ʹÓù«Óñí±í´ïʽ£¨CTE£©¼ò»¯Ç¶Ì×SQL ºÍ½øÐеݹéµ÷ÓÃ
1.ʹÓÃCTE¼ò»¯Ç¶Ì×sql
ÏÈ¿´ÏÂÃæÒ»¸öǶÌ׵IJéѯÓï¾ä£º
select * from person.StateProvince where CountryRegionCode in
(select CountryRegionCode from person.CountryRegion where Name like 'C%')
ÉÏÃæµÄ²éѯÓï¾äʹÓÃÁËÒ»¸ö×Ó²éѯ¡£ËäÈ»ÕâÌõSQLÓï¾ä²¢²»¸´ÔÓ£¬µ«Èç¹ûǶÌ׵IJã´Î¹ý¶à£¬»áʹSQLÓï¾ä·Ç³£ÄÑÒÔÔĶÁºÍά»¤¡£Òò´Ë£¬Ò²¿ÉÒÔʹÓñí±äÁ¿µÄ·½Ê½À´½â¾öÕâ¸öÎÊÌ⣬SQLÓï¾äÈçÏ£º
declare @t table(CountryRegionCode nvarchar(3))
insert into @t(CountryRegionCode) (select CountryRegionCode from person.CountryRegion where Name like 'C%')
select * from person.StateProvince where CountryRegionCode
in (select * from @t)
ËäÈ»ÉÏÃæµÄSQLÓï¾äÒª±ÈµÚÒ»ÖÖ·½Ê½¸ü¸´ÔÓ£¬µ«È´½«×Ó²éѯ·ÅÔÚÁ˱í±äÁ¿@tÖУ¬ÕâÑù×ö½«Ê¹SQLÓï¾ä¸üÈÝÒ×ά»¤£¬µ«ÓÖ»á´øÀ´ÁíÒ»¸öÎÊÌ⣬¾ÍÊÇÐÔÄܵÄËðʧ¡£ÓÉÓÚ±í±äÁ¿Êµ¼ÊÉÏʹÓÃÁËÁÙʱ±í£¬´Ó¶øÔö¼ÓÁ˶îÍâµÄI/O¿ªÏú£¬Òò´Ë£¬±í±äÁ¿µÄ·½Ê½²¢²»Ì«ÊʺÏÊý¾ÝÁ¿´óÇÒƵ·±²éѯµÄÇé¿ö¡£Îª´Ë£¬ÔÚSQL Server 2005ÖÐÌṩÁËÁíÍâÒ»ÖÖ½â¾ö·½°¸£¬Õâ¾ÍÊǹ«Óñí±í´ïʽ£¨CTE£©£¬Ê¹ÓÃCTE£¬¿ÉÒÔʹSQLÓï¾äµÄ¿Éά»¤ÐÔ£¬Í¬Ê±£¬CTEÒª±È±í±äÁ¿µÄЧÂʸߵöࡣ
ÏÂÃæÊÇCTEµÄÓï·¨£º
[ WITH <common_table_expression> [ ,n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,n ] ) ]
AS
( CTE_query_definition )
ÏÖÔÚʹÓÃCTEÀ´½â¾öÉÏÃæµÄÎÊÌ⣬SQLÓï¾äÈçÏ£º
with
cr as
(
select CountryRegionCode from person.CountryRegion where 
Ïà¹ØÎĵµ£º
¡¾ÎÊÌâÒ»
¡¿£ºÒѾܾø¶Ô OLE DB Ìṩ³ÌÐò 'MICROSOFT.JET.OLEDB.4.0' µÄÌØÊâ·ÃÎÊ¡£±ØÐëͨ¹ýÁ´½Ó·þÎñÆ÷À´·ÃÎÊ´ËÌṩ³ÌÐò¡£
¡¾·ÖÎö
¡¿£ºÕâÊÇÒòΪÄúµ±Ç°ÓÃÀ´·ÃÎÊÊý¾Ý¿âµÄµÇ¼µÄȨÏÞ²»¹»£¨È¨ÏÞÌ«µÍ£©¡£
¡¾½â¾ö
¡¿£ºÒªÃ´Äú»¹ÓÃsaµÇ¼£¬ÒªÃ´Ð޸ĵ±Ç°µÇ¼µÄȨÏÞ£¬²½ÖèÈçÏ£ºÆóÒµ¹ÜÀíÆ÷→°²È«ÐÔ→µÇ¼→ÕÒµ½ÄãËùÓõ ......
--²éѯӦÓóÌÐòµÄµÈ´ý
SELECT TOP 10
wait_type,waiting_tasks_count AS tasks,
wait_time_ms,max_wait_time_ms AS max_wait,
signal_wait_time_ms AS signal
from sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
--²éѯÔÚÈÎһʱ¿ÌËùÓÐÊÚȨ¸øµ±Ç°Ö´ÐÐÊÂÎñ»òµ±Ç°Ö´ÐÐÊÂÎñµÈ´ýµÄËø
SELECT
request_session_id A ......
BackupEveryDay
ÿÌì½øÐÐÊý¾Ý¿âµÄ²îÒ챸·Ý
day
Declare @File Varchar(2000)
Set @File='E:\Databasebackup\njyc_data_diff.BAK'
Backup database njyc_data to Disk=@File with DIFFERENTIAL
WeekBackup
ÿÖܽøÐÐÒ»´ÎÊý¾Ý¿âµÄÍêÈ«±¸·Ý£¬±¸·ÝÎļþÃûΪµ±ÌìÈÕÆÚ £¨njyc_data_Äê_ÔÂ_ÈÕ£©
BackupAll
DECLARE @BackupFi ......
DECLARE @sql varchar(8000)
--ÏÂÃæÊÇɾ³ý15ÌìÇ°±¸·ÝÎļþµÄ´¦Àí
set @sql='Del E:\Databasebackup\'
@sql=@sql +rtrim(convert(varchar,getdate()-15,112))+'.bak'
Exec master..xp_cmdshell @sql
DECLARE @DelFile Varchar(256)
set ......
ÒýÓÃ×Þ½¨µÄ£º
Cod
SELECT
±íÃû=case when a.colorder=1 then d.name else '' end,
×Ö¶ÎÐòºÅ=a.colorder,
......