一些資料庫操作的常用技巧（陸續有加）

關鍵字：SQL Server 資料庫 引言            平時做開發不可避免的與資料庫打交道，總會遇到這樣或那樣的問題，遇到難題就goole一下，下次遇到有時還要Google，乾脆平時積累一下遇到同樣問題解決也就方便多了，下面就是我平時積累和搜集的一些常見問題的解決技巧的積累，希望對大家也能有協助。 閱讀對象

• 對SQL Server有開發經驗
• 其它對單元測試有興趣的人員

1) 跨伺服器資料庫操作

        這個對於DBA可能要經常用到。假設：有兩個資料庫DB1，DB2，DB1下有T1表，DB2下有T2表，目前要查詢T1的欄位F1和T2下欄位F2相同的記錄。目前查詢分析器連結的DB1。
        方案一：使用OPENDATASOURCE直接進行查詢。
                   SQL語句範例：

       select T1.*, T2.* from T1, OPENDATASOURCE('SQLOLEDB','Data Source=192.168.0.5;User ID=sa;Password=sa;').DB2.dbo.T2 as T2 where t1.F1 = t2.F2  

        方案二：為DB1所在資料庫使用 sp_addlinkedserver 新增一個連結的伺服器 進行查詢。
                  操作範例：

            EXEC  sp_addlinkedserver@server='LocDB2',--被訪問的伺服器別名@srvproduct='',@provider='SQLOLEDB',@datasrc='192.168.0.5'   --要訪問的伺服器EXEC sp_addlinkedsrvlogin'LocDB2', --被訪問的伺服器別名'false',NULL,'sa', --帳號'sa' --密碼select T1.*, T2.* from T1, LocDB2.DB2.dbo.T2 as T2 where T1.F1= T2.F2

當然：如果DB2和DB1在同一台技巧上，並且當前登入DB1的使用者有權訪問DB2，那麼就沒有必要這麼複製了，直接使用

        select T1.*, T2.* from T1, DB2.dbo.T2 as T2 where T1.F1= T2.F2 

2)  不同定序(COLLATE)的查詢

這種現象我經常碰到，因為我在台資公司工作，當時我還是喜歡簡體，因此我的資料庫的定序基本上都是簡體，而好多其它的大部分用的繁體，因此不同的欄位比較時就出現了此問題。 比如資料庫DB1，表T1，F1欄位的定序為 Chinese_PRC_CI_AS， F2欄位為 Chinese_Taiwan_Stroke_CI_AS，查詢 此表中F1和F2欄位相等的記錄。

        select * from T1   where F1= F2  COLLATE Chinese_PRC_CI_AS 

COLLATE Chinese_PRC_CI_AS 的含義強制都轉換為 Chinese_PRC_CI_AS 格式進行比較。

3)  動態列查詢

這是需求做系統報表時常見的一個查詢統計需求。
需求描述：比如有一個成績表，包含姓名(realname)，課程(Class)， 成績等級(grade)，未經處理資料如

但是我想查詢出如右

查詢語句實現

        declare   @s   nvarchar(4000)set   @s=''select  @s=@s+','+quotename(class)+ '=isnull(rtrim(max(case   Class   when   ' +quotename(Class,'''')+'   then   Score   end)),'''')'from   TSC   group   by   class--   print @S   --這裡輸出@S 你可以看到動態組織的SQL 陳述式的原貌exec('select RealName as ''項目\科目''' +  @s + '   from   TSC   group   by   RealName  ')

當然這裡面有一個缺陷就是，這裡的class不能太多，否則 @S 超過系統所承受的最大長度您就無法得到查詢結果了

4) 查詢表中以某欄位為分類，另外一個欄位滿足制定條件的記錄資訊

            例如T1表有如下資料（左圖）而查詢結果（右圖）。要求為：查詢F2欄位內容相同，F3數值最大的所有記錄。

  
                未經處理資料                                                       目的結果

        解決方案一：首先建立一個以F2資料為結果集合的視圖資料集合。然後建立指定指定為輸出結果的查詢函數。
                            1.1)建立視圖

       CREATE VIEW dbo.V_T_DistinctASSELECT DISTINCT F2 FROM dbo.T1

                  1.2       建立輸出F1， F3的函數，這裡要查詢輸出幾個欄位，就要寫幾個函數，因此此方法對於要很多欄位，並且都要輸出的表非常不方便。

--  建立輸出F1的函數CREATE FUNCTION dbo.fun_Get_F1 (@F2 varchar(16))RETURNS  int  ASBEGINdeclare @Ret intdeclare @MaxF3 varchar(16)select  @MaxF3 =  max(F3) from T1 where F2 = @F2select @Ret = F1 from T1 where  F2 = @F2 and @MaxF3 = F3return @RetENDGO--  建立輸出F3的函數CREATE FUNCTION dbo.fun_Get_F3 (@F2 varchar(16))RETURNS   varchar(16)  ASBEGINdeclare @MaxF3 varchar(16)select  @MaxF3 =  max(F3) from T1 where F2 = @F2return @MaxF3END

           1.3  執行最後的查詢語句，輸出查詢結果

        select dbo.fun_Get_F1(F2) as F1,F2, dbo.fun_Get_F1(F2) as F3 from V_T_Distinct 

       解決方案二： 首先建立一個能夠定位所需記錄的關鍵字的視圖，然後用表關聯查詢結果
          2.1 建立定位記錄關鍵字視圖

              CREATE VIEW dbo.V_T1ASSELECT F2, MAX(F3) AS Max_F3FROM dbo.T1GROUP BY F2

           下面是此視圖結果集

           2.2  組織查詢語句輸出查詢結果

              select T1.* from T1,V_T1 where T1.F2=V_T1.F2 and F3=Max_F3

這種方法比較簡單，推薦使用。