{"id":319502,"date":"2024-01-20T15:17:38","date_gmt":"2024-01-20T07:17:38","guid":{"rendered":"https:\/\/www.idc.net\/help\/319502\/"},"modified":"2024-01-20T15:17:38","modified_gmt":"2024-01-20T07:17:38","slug":"sql-server%e5%88%86%e5%8c%ba%e8%a7%86%e5%9b%be","status":"publish","type":"post","link":"https:\/\/idc.net\/help\/319502\/","title":{"rendered":"SQL SERVER\u5206\u533a\u89c6\u56fe"},"content":{"rendered":"

\u501f\u52a9SQL SERVER\u5206\u533a\u89c6\u56fe\uff0c\u53ef\u4ee5\u5bf9SQL\u4e2d\u7684\u8868\u8fdb\u884c\u96c6\u4e2d\u7ba1\u7406\uff0c\u4e0b\u6587\u5c06\u4ee5\u5b9e\u4f8b\u7684\u65b9\u5f0f\u4e3a\u60a8\u8be6\u89e3SQL SERVER\u5206\u533a\u89c6\u56fe\uff0c\u5e0c\u671b\u5bf9\u60a8\u5b66\u4e60SQL\u6570\u636e\u5e93\u80fd\u6709\u6240\u5e2e\u52a9\u3002<\/p>\n

SQL SERVER\u5206\u533a\u89c6\u56fe\u7ed9\u6211\u4eec\u63d0\u4f9b\u4e86\u4e00\u79cd\u5b9e\u73b0\u5927\u6570\u636e\u91cf\u7ba1\u7406\u7684\u65b9\u6cd5\uff0c\u7c7b\u4f3c\u4e8e\u5176\u5b83\u6570\u636e\u5e93\u7ba1\u7406\u7cfb\u7edf\u7684\u5206\u533a\u89c6\u56fe\u3002\u4e0d\u4ec5\u5b9e\u73b0\u4e86\u8868\u7684\u96c6\u4e2d\u7ba1\u7406\uff0c\u800c\u4e14\u5b9e\u73b0\u4e86\u6570\u636e\u7684\u5feb\u901f\u5b9a\u4f4d\u3002\u4e0b\u8fb9\u6211\u5c31\u4ee5\u4e00\u4e2a\u65e5\u5fd7\u8bb0\u5f55\u8868\u4e3a\u4f8b\u6765\u8bf4\u4ee5\u4e0b\u5206\u533a\u89c6\u56fe\u7684\u4f5c\u7528:<\/p>\n

\u65e5\u5fd7\u8bb0\u5f55\u8868\u53ef\u4ee5\u8bb0\u5f55\u5f88\u957f\u65f6\u95f4\u7684\u65e5\u5fd7\u4fe1\u606f\uff0c\u968f\u7740\u65f6\u95f4\u7684\u63a8\u79fb\uff0c\u65e5\u5fd7\u8868\u4f1a\u53d8\u7684\u5f88\u5927\u3002\u8fd9\u6837\u5bf9\u4e0e\u8868\u7684\u67e5\u8be2\u548c\u7ef4\u62a4\u64cd\u4f5c\u90fd\u662f\u975e\u5e38\u6162\u7684\uff0c\u4f46\u662f\u5982\u679c\u6211\u4eec\u6309\u65e5\u671f\u6765\u5212\u5206\u628a\u8868\u5206\u6210\u82e5\u5e72\u5c0f\u8868\uff0c\u800c\u5728\u903b\u8f91\u4e0a\u7edf\u4e00\u4f7f\u7528\u4e00\u4e2a\u89c6\u56fe\u6765\u67e5\u8be2\u7ba1\u7406\uff0c\u90a3\u4e48\u662f\u975e\u5e38\u65b9\u4fbf\u7684.\u800c\u4e14SQL SERVER\u53ef\u4ee5\u6839\u636e\u67e5\u8be2\u7684\u6761\u4ef6\u6765\u81ea\u52a8\u7684\u5b9a\u4f4d\u8868\uff0c\u8fd9\u6837\u4e5f\u5c31\u63d0\u9ad8\u4e86\u67e5\u8be2\u7684\u901f\u5ea6.<\/p>\n

-- \u9996\u5148\u6211\u4eec\u8981\u5b9e\u73b0\u7684\u662f\u4e00\u4e2a\u8bb0\u5f55\u65e5\u5fd7\u7684\u5b58\u50a8\u8fc7\u7a0b\uff0c\u8fd9\u4e2a\u5b58\u50a8\u8fc7\u7a0b\u4e0d\u662f\u7b80\u5355\u7684\u628a\u65e5\u5fd7\u63d2\u5165\uff0c\u8fd8\u8981\u6309\u5f53\u524d\u65e5\u671f\u6765\u5224\u65ad-- \u8868\u662f\u5426\u5b58\u5728\uff0c\u7136\u540e\u52a8\u6001\u7684\u5efa\u7acb\u8868\uff0c\u5e76\u6309\u5206\u533a\u8868\u7684\u89c4\u5219\u5efa\u7acbCHECK\u7ea6\u675f.\u5728\u6b64\u4f8b\u4e2d\uff0c\u6211\u4eec\u4f7f\u7528\u7684\u662f\u6309\u5468\u7684\u65e5\u671f\u6765-- \u89c4\u5b9a\u8868\u7684\u540d\u79f0\u5982\u4e0b:dbo.[20061218-20061224]<\/p>\n

\n \n \n 
    \n  \n  \n  
  1. use TestDB  <\/span><\/span><\/li>\n  \n  \n  
  2. if exists (select name from sysobjects where <\/span>name<\/span> = <\/span>'P_LogWriter'<\/span> and <\/span>type<\/span> = <\/span>'P'<\/span>)  <\/span><\/li>\n  \n  \n  
  3.    drop proc dbo.P_LogWriter  <\/span><\/li>\n  \n  \n  
  4. go  <\/span><\/li>\n  \n  \n  
  5. ----Log\u65e5\u5fd7\u6587\u4ef6  <\/span><\/li>\n  \n  \n  
  6.  <\/span><\/li>\n  \n  \n  
  7. create proc dbo.P_LogWriter  <\/span><\/li>\n  \n  \n  
  8. @desc varchar(1000)  <\/span><\/li>\n  \n  \n  
  9. as  <\/span><\/li>\n  \n  \n  
  10. begin tran  <\/span><\/li>\n  \n  \n  
  11. set nocount on  <\/span><\/li>\n  \n  \n  
  12.  <\/span><\/li>\n  \n  \n  
  13. declare @wk_start datetime,@wk_end datetime  <\/span><\/li>\n  \n  \n  
  14. declare @str_start varchar(50),@str_end varchar(50)  <\/span><\/li>\n  \n  \n  
  15. declare @tb_name varchar(100),@sql varchar(4000)  <\/span><\/li>\n  \n  \n  
  16.  <\/span><\/li>\n  \n  \n  
  17. -- \u5468\u7684\u5f00\u59cb\u65e5\u671f  <\/span><\/li>\n  \n  \n  
  18. set @<\/span>wk_start<\/span>=<\/span>dateadd<\/span>(d,2-datepart(dw,getdate()),convert(char(10),getdate(),120))  <\/span><\/li>\n  \n  \n  
  19. -- \u5468\u7684\u7ed3\u675f\u65e5\u671f  <\/span><\/li>\n  \n  \n  
  20. set @<\/span>wk_end<\/span>=<\/span>dateadd<\/span>(s,-1,dateadd(wk,1,@wk_start))  <\/span><\/li>\n  \n  \n  
  21.  <\/span><\/li>\n  \n  \n  
  22. set @<\/span>str_start<\/span>=<\/span>convert<\/span>(varchar(50),@wk_start,120)  <\/span><\/li>\n  \n  \n  
  23. set @<\/span>str_end<\/span>=<\/span>convert<\/span>(varchar(50),@wk_end,120)  <\/span><\/li>\n  \n  \n  
  24.  <\/span><\/li>\n  \n  \n  
  25. -- \u8868\u7684\u540d\u79f0  <\/span><\/li>\n  \n  \n  
  26. set @<\/span>tb_name<\/span>=<\/span>convert<\/span>(varchar(10),@wk_start,112)+'-'+convert(varchar(10),@wk_end,112)  <\/span><\/li>\n  \n  \n  
  27.  <\/span><\/li>\n  \n  \n  
  28. -- \u5982\u679c\u8868\u4e0d\u5b58\u5728\uff0c\u5c31\u5efa\u7acb\u8868  <\/span><\/li>\n  \n  \n  
  29. if not exists(select 1 from sysobjects where <\/span>name<\/span>=@tb_name and <\/span>xtype<\/span>=<\/span>'U'<\/span>)  <\/span><\/li>\n  \n  \n  
  30. begin  <\/span><\/li>\n  \n  \n  
  31.    set @<\/span>sql<\/span>=<\/span>'create table dbo.['<\/span>+@tb_name+'](id int identity(1,1),updatetime datetime primary key check(updatetime between '''+@str_start+''' and '''+@str_end+'''),[desc] varchar(1000))'  <\/span><\/li>\n  \n  \n  
  32.    --print @sql  <\/span><\/li>\n  \n  \n  
  33.    exec(@sql)  <\/span><\/li>\n  \n  \n  
  34. end  <\/span><\/li>\n  \n  \n  
  35.  <\/span><\/li>\n  \n  \n  
  36. -- \u63d2\u5165\u76f8\u5e94\u7684\u65e5\u5fd7\u6570\u636e  <\/span><\/li>\n  \n  \n  
  37. set @<\/span>sql<\/span>=<\/span>'insert ['<\/span>+@tb_name+'](updatetime,[desc]) select getdate(),'''+@desc+''''  <\/span><\/li>\n  \n  \n  
  38. exec(@sql)  <\/span><\/li>\n  \n  \n  
  39.  <\/span><\/li>\n  \n  \n  
  40. set nocount off  <\/span><\/li>\n  \n  \n  
  41. commit tran  <\/span><\/li>\n  \n  \n  
  42. Go  <\/span><\/li>\n  \n  \n  
  43.  <\/span><\/li>\n \n \n <\/ol><\/pre>\n
    #p#-- \u521b\u5efa\u5206\u533a\u89c6\u56fe<\/p>\n
    \n \n \n 
      \n  \n  \n  
    1. create view v_Log  <\/span><\/span><\/li>\n  \n  \n  
    2. as  <\/span><\/li>\n  \n  \n  
    3. select * from dbo.[20061218-20061224]  <\/span><\/li>\n  \n  \n  
    4. union all  <\/span><\/li>\n  \n  \n  
    5. select * from dbo.[20061225-20061231]  <\/span><\/li>\n  \n  \n  
    6. Go <\/span><\/li>\n \n \n <\/ol><\/pre>\n
      -- \u67e5\u8be2\u5206\u533a\u89c6\u56fe<\/p>\n
      \n \n \n 
        \n  \n  \n  
      1. select * from v_Log  <\/span><\/span><\/li>\n  \n  \n  
      2. where updatetime<\/span><<\/span>'2006-12-24 11:21:27.653' <\/span><\/li>\n \n \n <\/ol><\/pre>\n
        -- \u901a\u8fc7\u5206\u6790\u67e5\u8be2\u7684\u6267\u884c\u8ba1\u5212\u6211\u4eec\u53ef\u4ee5\u770b\u5230\uff0cSQL SERVER\u53ef\u4ee5\u81ea\u52a8\u5b9a\u4f4d\u76f8\u5e94\u7684\u8868\u53d6\u5f97\u6570\u636e\uff0c\u4ece\u800c\u63d0\u9ad8\u4e86\u67e5\u8be2\u7684-- \u901f\u5ea6.
          <\/p>\n
         <\/p>\n
         <\/p>\n
        \u3010\u7f16\u8f91\u63a8\u8350\u3011<\/p>\n
        \u6559\u60a8\u5982\u4f55\u4f7f\u7528SQL\u4e2d\u7684\u51fd\u6570\u66ff\u4ee3\u6e38\u6807<\/p>\n
        \u521b\u5efaSQL\u51fd\u6570\u7684\u5b9e\u4f8b<\/p>\n
        SQL\u4e2d\u8fd4\u56de\u8ba1\u7b97\u8868\u8fbe\u5f0f\u7684\u51fd\u6570<\/p>\n
        SQL\u4e2d\u5faa\u73af\u8bed\u53e5\u7684\u6548\u679c\u5b9e\u4f8b<\/p>\n
        SQL\u4e2d\u7c7b\u4f3cFor\u5faa\u73af\u5904\u7406\u7684\u5b9e\u4f8b<\/p>\n","protected":false},"excerpt":{"rendered":"
        \u501f\u52a9SQL SERVER\u5206\u533a\u89c6\u56fe\uff0c\u53ef\u4ee5\u5bf9SQL\u4e2d\u7684\u8868\u8fdb\u884c\u96c6\u4e2d\u7ba1\u7406\uff0c\u4e0b\u6587\u5c06\u4ee5\u5b9e\u4f8b\u7684\u65b9\u5f0f\u4e3a\u60a8\u8be6\u89e3SQL SERVE […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7038],"tags":[],"class_list":["post-319502","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/posts\/319502","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/comments?post=319502"}],"version-history":[{"count":0,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/posts\/319502\/revisions"}],"wp:attachment":[{"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/media?parent=319502"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/categories?post=319502"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/idc.net\/help\/wp-json\/wp\/v2\/tags?post=319502"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}