mssql論壇集錦

一、
題目：請教複雜SQL問題
我有兩個表， 假如資料如下
表1
ID     A     B                                  
--------------                                  
1      A1   B1                                  
1      A2   B2                                  
表2
ID          C        D
-----------------------
1           C1      D1
1           C2      D2
1           C3      D3
請問我有什麼辦法，可以將上面兩個表的資料，查詢得到如下
同一個ID，有時表1的行數多，有時表2的行數多
ID     A     B     C        D
------------------------------------
1      A1   B1   C1      D1
1      A2   B2   C2      D2
1                    C3      D3
解決：
select identity(int,1,1) rid,id,a,b into #t1 from 表1;
select identity(int,1,1) rid,id,a,b into #t2 from 表2;
select a.id,a.a,a.b,b.c,b.d from #t1 a full join #t2 b on a.rid=b.rid order by b.rid;
二、
題目：為什麼then @sum=@sum+1 else @sum end 有誤？
declare @i smallint
declare @sum int
set @i=1
set @sum=0
while @i<=8
  begin
  select cno,  case when
    'F'+cast(@i as varchar(3))='T' then @sum=@sum+1 else @sum end Tsum
    from tb2
    set @i=@i+1
end
**************
伺服器: 訊息 170，級別 15，狀態 1，行 8
第 8 行: '=' 附近有語法錯誤。
伺服器: 訊息 156，級別 15，狀態 1，行 11
在關鍵字 'end' 附近有語法錯誤。
****************
解決：
declare @i smallint
declare @sum int
set @i=1
set @sum=0
while @i<=8
begin
  select cno,  case when
    'F' + cast(@i as varchar(3)) = 'T'
    then @sum + 1 else @sum end Tsum
  from tb2

  set @i=@i+1

end
三、
題目：SQL2000完全備份策略請教
我想請教個問題，A資料庫每週一做一次完全備份，每天做一次差異備份，然後每小時做一次日誌備份。
我想問，比如我在星期三手工的做了一次完全備份，那之後的差異備份(週四到週日)就是在我手工備份的基礎上產生的了？那在這一週內，如果週五如果資料出了問題，以下方法可以還原嗎？
1.還原週一的完全備份，接著還原每天的連續的日誌備份
2.還原週一的完全備份，還原週二的差異備份，還原週二到週五的日誌備份
3.還原週三的完全備份，還原週五的差異備份，還原週五的日誌備份
請問是這樣嗎？還有其他方法嗎？
如果我在這期間手動備份了資料庫，那差異備份就是在我手動備份資料庫的基礎上差異了？那我之前自動備份的那個資料庫就不能應用差異備份恢復了，是這樣嗎？

解決：
恩，差異備份應該是基於上一次的完全備份基礎上的，那這樣來說的話，一般情況下我們就沒必要手動的去備份資料庫了，只用將系統自動備份的資料庫轉存到它處，安全起見再把差異備份和日誌備份一同轉存，這樣就可以了吧。
下面的備份策略怎麼樣：
1。每週末00：00完全備份資料庫
2。每天1：00差異備份資料庫
3。每隔4個小時日誌備份
每週為一個迴圈，這樣的話會產生1個完全備份，7個差異備份，42個日誌備份，一共就是50個備份檔案。
一共有2個資料庫，均為6G左右，活動不是很頻繁，這樣的備份策略合適嗎？

我答：
不好意思，我記錯了，差異備份是以最近一次全備為基礎的，而且備份後系統標誌的單位也不是以資料塊為單位，而是以EXTENTS為單位。
合適不合適不好說，至少我覺得是安全的。其實，這麼小的資料庫，而且活動不頻繁，我覺得有點繁瑣，差異備份一般是針對很大的資料庫做的，不過，個人感覺合適就是合適，對吧。
四、
問題：
一個PL/SQL語句如何轉換為SQL SERVER 語句

begin
for  fm in (SELECT a.zgh,b.xm  
             FROM gzxt a,zgxx11 b  
             WHERE  a.xm is null  and a.zgh=b.zgh) loop
    update gzxt u
       set u.xm=fm.xm
     where u.zgh= fm.zgh
       and u.xm is null;
end loop;
end;
解決：
Update gzxt Set XM=b.xm
  From gzxt a Join gzxx11 b on a.zgh=b.zgh
  Where a.xm is null

測試下，沒驗證過
也不知道這個ORACLE對這個迴圈的效能能作如何最佳化，還是開發者喜歡玩ROW而不玩SETS
五、問題：
緊急提問，批次修改問題。
我這有個mssqlserver的資料庫，其中一張表是有個欄位名是docno（檔案號),欄位型別為int，4位，但是在實際錄入資料時候，由於沒有加約束和規則，可以錄入5位，既10000以上的編碼。現在要求把這這些1000以上的編碼改為4位，請問如何改。
例如：   修改前   docno      修改後   docno
                           61051                    1051
                           61075                    1075
                           61078                     1078
我想批次一次性改正過來，請問如何編寫這個語句。
謝謝！！！
解決：
update doc set docno=substring(docno,2,4);
update doc set docno=substring(cast(docno as varchar),2,4);

六、
問題：[求助]SQL字串刪除語句
SQL求助,求教一個語句:一個表,判斷一個欄位裡面的內容,如果有含某個字元,比如含字元'a',就把該字元後面的所有內容刪除.
比如一個表的TXT欄位裡的第一記錄內容為:fair,第二記錄內容為:abbe,第三記錄的內容為:monday,執行語句後的各記錄分別為:f,null,mond
謝謝呀!
解決：
select left(txt,CHARINDEX('a',txt)-1) from test1；
七、
問題：求救關於isnumeric問題

表table_1
欄位 jg_1
值列表如下

jg_1
0.01
0.02
100.1
正
負
-
-
語句如下
select CASE isnumeric(jg_1) WHEN 1 THEN CONVERT(decimal(12, 3), jg_1) ELSE NULL  END) AS jg_num from table_1
報錯
將 varchar 轉換為資料型別 numeric 時出現算術溢位錯誤

經測試，如果是漢字，isnumeric是有用的，但是如果是-這樣的字元就出錯了。

那麼這個語句要怎樣寫才能轉換

[ 本帖最後由 lky9999 於 2008-8-21 11:49 編輯 ]
解決：
這樣當然是可以了。
和樓主的問題還是有差別。
樓主的問題就處在了'-','+','$'等這些非NUMERIC型別的字元，在用isnumeric時，會返回1，也會按照numeric型別進行轉換，這是不可能的，從而導致出錯。
select CASE isnumeric(jg_1) WHEN 1 THEN CONVERT(decimal(12, 3), jg_1)   ELSE NULL  END AS jg_num
from table_1
where jg_1 not in('-','+','$');
排除這些值。
八、
問題：
如何給一個使用者賦予整個資料庫的許可權？

捎帶負責一套SQLServer的資料庫。
現在，需要給一個使用者賦予一個資料庫中所有表的增刪改查許可權
                                  和所有儲存過程、觸發器的執行許可權，
因為表很多，儲存過程也有500個左右，有沒有簡便一些的方法，把上面的許可權賦上？多謝了！

環境：Win2003 + SQL2005


__________________
芝蘭生於深林，不以無人而不芳！
關注：Oracle9i/10g/11g+linux/unix+儲存技術
DBA逐漸多元化了：SQL、Oracle、DB2都要搞起來！
紙上得來終覺淺，絕知此事要躬行
子在川上曰：逝者如斯夫，不捨晝夜。時間怎麼過得這麼快呢？
E-mail:sdusun0819@hotmail.com
解決：
老弟，別來無恙？
use test;
go
EXEC sp_addrolemember 'db_datareader', 'test';
EXEC sp_addrolemember 'db_datawriter', 'test';        
--給資料庫使用者db_username授予表許可權；

use test;
go
select 'grant execute on '+name+' to db_username' from test.sys.triggers;
select 'grant execute on '+name+' to db_username' from test.sys.procedures where name not like 'sp%';

--執行生成的指令碼，給資料庫使用者db_username授予觸發器、過程執行許可權；
僅供參考

[ 本帖最後由 sqysl 於 2009-1-24 10:36 編輯 ]


__________________
QQ:503318229
msn:sqysl@hotmail.com
Blog:sqysl.itpub.net

九：
題目：
請問如果授予一個使用者只讀資料庫的許可權

資料庫為sql server 2000，一個只讀資料庫DBTest ，請問如何授予使用者test訪問只讀資料的DBTest的許可權？（先去除只讀再新增許可權的方法不算）

解決：
CREATE LOGIN tt
    WITH PASSWORD = 'tt#tt';
USE test;
CREATE USER tt_db FOR LOGIN tt;
GO
EXEC sp_addrolemember 'db_datareader', 'tt_db';
GO
十、問題

請教大家一個SQL的寫法？

表名：TB
欄位：channel   varchar(50) 訪問頻道
      visittime   varchar(50) 訪問時間 庫裡記錄儲存的形式是：yyyy-mm-dd hh:mm:ss  例如：2009-02-01 16:58:30
我想統計 頻道名字 為“五”的 每個小時的訪問次數？就是8點、9點、10點......的訪問次數？
謝謝各位！
解決：
SELECT channel,sum(CASE DATEPART(HOUR,visittime)
                WHEN 8 THEN 1
                ELSE 0 END
                ) as '8',
sum(CASE DATEPART(HOUR,visittime)
                WHEN 9 THEN 1
                ELSE 0 END
                ) as '9',
sum(CASE DATEPART(HOUR,visittime)
                WHEN 10 THEN 1
                ELSE 0 END
                ) as '10'
FROM TB
WHERE channel='五'
GROUP BY channel;