T_SQL 開發的13個Tips

1 類型轉換  Cast(10.947 AS INT) 或Convert(INT,10.947)

2  經常用到的函數
newid() 用於建立GUID值，round(price,2) 四捨五入
isnull(summary,0) 如果summary為空白，則返回0
substring(‘abcdefg’,2,3)  取子字串
replace(‘abcdefg’,’abc’,’123’) 替換字串
rand() 求0-1之間的隨機數  
dateAdd(day, 21, getdate()) 在向指定日期加上一段時間的基礎上，返回新的 datetime 值
datePart(month, GETDATE())  返回代表指定日期的指定日期部分的整數

3 動態構建SQL
exec (‘  SELECT [job_id], [job_desc] FROM [pubs].[dbo].[jobs] ‘)
或 exec sp_executesql N’SELECT [job_id], [job_desc] FROM [pubs].[dbo].[jobs]’
欄位名，表名，資料庫名作變數時，必須用動態SQL
declare @fname varchar(20)
set @fname='job_id'
exec (' SELECT '+ @fname+ ' FROM [pubs].[dbo].[jobs] ')

4  在資料匯入匯出功能時，如果是SQL Server 之間相互拷貝資料，並且表的欄位類型含有identity值，則應該使用下面的指令碼關閉自增，匯入匯出完畢後再開啟
set identity_insert  表名  on   --關閉表的identity屬性作用
set identity_insert   表名  off   --開啟表的identity屬性作用

5  CASE語句的寫法
SELECT  Price=CASE WHEN  Price  IS NULL THEN ‘not yeat’
                               WHEN  Price<10 THEN ‘Cheap’
                                WHEN Price>10 THEN ‘Expensive’
                         END
或是把需要比較的列值放到when的後面。
SELECT Gender=
  CASE 1 THEN ‘男’
  CASE 0  THEN ‘女’ 
  ELSE ‘not yet’
END
CASE 語句常用於行列轉換
SELECT 部門,
[材料1]= SUM(CASE 材料 WHEN ‘材料1’ THEN 數量 ELSE 0 END) ，
[材料2]= SUM(CASE 材料 WHEN ‘材料2’ THEN 數量 ELSE 0 END)
FROM 部門耗材
GROUP BY 部門

6 分頁 查詢第X頁,每頁Y條記錄
如果表中有主鍵
select top y * from 表 where 主鍵 not in(select top (x-1)*y 主鍵 from 表)
如果表中無主鍵,可以用暫存資料表,加識別欄位解決
select id=identity(int,1,1),*  into #tb from 表
select * from #tb where id between (x-1)*y and x*y-1

7 EXISTS的用法
SELECT DISTINCT pub_name   FROM publishers
WHERE EXISTS
   (SELECT *    FROM titles    WHERE pub_id = publishers.pub_id    AND type = 'business')

8  流水號產生
產生長度為8的編號，編號以BH開頭，其餘6位為流水號
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO
--在表中應用函數
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)
9 
產生的編號長度為12，前6位為日期資訊，格式為YYMMDD，後6位為流水號
--建立得到當前日期的視圖
CREATE VIEW v_GetDate  AS
SELECT dt=CONVERT(CHAR(6),GETDATE(),12) GO
--得到新編號的函數
CREATE FUNCTION f_NextBH()
RETURNS char(12)
AS
BEGIN
    DECLARE @dt CHAR(6)
    SELECT @dt=dt FROM v_GetDate
    RETURN(
        SELECT @dt+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6)
        FROM tb WITH(XLOCK,PAGLOCK)
        WHERE BH like @dt+'%')
END
GO

 --在表中應用函數
CREATE TABLE tb(
BH char(12) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)

 

9  選擇不重複的字串，表的資料如下，假設列名為ID,表名為tbl
aaa
bbb
ccc
aaa
1)   DISTINCT  SELECT DISTINCT ID FROM tbl
2)   分組 SELECT  ID FROM tbl GROUP BY ID Having COUNT(*)>1 

10 字串用在where語句中判斷是否相等的時候，提示cannot resolve collection conflict for equal to operation,
應該加上database default

 

11 SQL Server 字串類型是大小寫不敏感的，aa,AA是同樣的含義。
有時候確需要實現大小寫字元敏感，比如密碼。aa,AA表示不同的密碼。
為實現字串大小寫敏感，可以把字串轉話為二進位後再作比較。
CAST(Password AS varbinary)

 

12 SQL 錯誤處理
檢測@@error變數的值，發生錯誤時，該值不為0
if @@error<>0
    print '發生錯誤1'
不是嚴重的錯誤,所以SQL會執行下去;
屬於嚴重的錯誤,所以SQL沒有執行下去;
被調用的預存程序發生嚴重錯誤時,調用它的預存程序可以捕獲錯誤,並可以繼續執行下去;
SQL Server 2005的用法
BEGIN TRY
DELETE FROM IPR WHERE TransationID= 1003
END TRY
BEGIN CATCH
END CATCH

 

13  子查詢
求工資最高的員工的姓名
SELECT name FROM Employee
  WHERE wage=(SELECT MAX(wage) FROM Employee)
求評論最多的文章
SELECT Title FROM Post
  WHERE Reply=(SELECT MAX(Reply) FROM Post)