[SQL] 自動取號 (數字左側補0至指定長度)

數字左側補0至指定長度的SQL語法如下

RIGHT(REPLICATE('0', 指定長度) + CAST('123' AS NVARCHAR), 指定長度)

例：RIGHT(REPLICATE('0', 10) + CAST('123' AS NVARCHAR), 10)

～～自動取號的部分～～

假如ID為英文2位數+數字10位數的組合

例：ID0000000001

首先先宣告變數

declare @new_id varchar(20)

接下來就重點戲了，查詢目前取號的最大值並拆解ID的數字部分+1後組裝回去

看一下現在取到第幾號及新的號碼是否正常

SELECT TOP(1) id AS '目前號碼', LEFT(id,2) + RIGHT(REPLICATE('0', 10) + CAST(CAST(RIGHT(id, 10) AS int) + 1 AS NVARCHAR), 10) AS '取新號碼' FROM ID_List ORDER BY id DESC

指派變數的值

set @form_id = (SELECT TOP(1) LEFT(id,2) + RIGHT(REPLICATE('0', 10) + CAST(CAST(RIGHT(id, 10) AS int) + 1 AS NVARCHAR), 10) FROM ID_List ORDER BY id DESC)

就可以使用變數來INSERT、UPDATE了

[MS SQL] 數值轉換的問題(AVG)

最近遇到的問題是將文字轉成數值計算平均值時，
數值型態會受到一開始轉換的型態影響。

例如
AVG(CONVERT(INT,[分數]))  -- 整數型態
AVG(CONVERT(FLOAT,[分數]))  --浮點數型態

所以在一開始就要定義好轉換後想要的型態
才能順利得出想要的結果
感覺挺微妙的


順道附上轉成小數點兩位的函數
CAST(AVG(CONVERT(FLOAT,[分數])) AS DECIMAL(10,2))

以上

[SQL] ROUND() 四捨五入

使用ROUND()的時候如果資料型態不是decimal或numeric的話，
會跑出像是四捨六入的結果

所以在使用ROUND()時，
還是將數值轉型會比較安全

例：
SELECT ROUND(CAST(欄位1 AS decimal(10,3)),2) FROM 資料表A

參考資料: TechNet

[SQL] UPDATE JOIN

有時候會遇到要UPDATE的資料表識別欄位不足的問題
需要JOIN別張表才能順利辨別出要更新的是那些欄位
(嘛...大概就是你要更新所有姓陳的男生資料，結果性別欄在別張表這種感覺)

這時候就很需要用JOIN把需要的欄位抓進來判別
SO，請參考下方語法 ～

[SQL] Random 亂數

一般的亂數在直接使用RAND()時容易出現一整片都是同個亂數的問題，
像這次我需要每列資料、甚至每欄資料要產生不同參數時，
就需要用NEWID()當作SEED，傳入RAND()中產生不同亂數。

以下為程式演練，可以參考看看！

[SQL] UPDATE FROM (使用別的表格或欄位更新資料)

總是會遇到需要將別的欄位或別的表格的資料更新到其他表的時候，
這時候你就會需要 UPDATE FROM

[SQL] 還原資料庫發生錯誤 "無法獲得獨佔存取權,因為資料庫正在使用中"

還原資料庫的時候，忘記斷開所有連結而出現這個錯誤訊息真的很.........啊雜
雖然可以下指令Kill多餘連線，或改為單一使用者模式，
但懶惰如我，真的不是很喜歡那些方式。

所以在這裡提供一個方法，就是直接讓資料庫離線再還原。
它還有一個好處，就是還原完資料庫會自動上線，不用再點選線上工作~

終於不用記那些落落長的指令了(灑花)

[SQL] ISNULL：當值為 NULL 時以他值取代

有時候 DBNULL 真的很討厭
想要讓它以我們想要的預設值出現
又或者
當 DBNULL 的時候傳回別欄的值
這時候 ISNULL函數就非常好用了

例：
SELECT  ISNULL(總成績, 0) , ISNULL(數甲成績, 數乙成績) FROM 成績總表

這時候第一欄當總成績為NULL時，就會代出0，
而第二欄是當數甲成績為NULL時，就會代出數乙成績。

[SQL] IF EXISTS

有些在進行動作前會想要得知某條件是否符合或是否有值
這時候 EXISTS 就非常好用了

程式碼如下：

 IF (EXISTS(SELECT * FROM Employee WHERE ID = '1234')) 
 BEGIN
       DELETE FROM info WHERE ID = '1234' ;
       INSERT INTO unit VALUES('1234','5678');
  END; 

大意為假如 EXISTS 後的指令有傳回值
則進行 BEGIN 與 END 間的指令

[MS SQL] 變數宣告與使用

以前還不覺得需要變數的時候，都是自己手動乖乖打上那些字串們 ~
算是輕鬆愉快也不覺得辛苦 ~

直到最近遇見了大魔王...
要用同一個參數查十多張表，還要由A表搜出的欄位當做變數去搜B.C.D.E表
再這樣複製貼上我就要崩潰啦！！

有句話說得好 ~
科技來自於人性（懶惰）
所以我只好習得本技能 - SQL指令的變數宣告與使用

首先你要宣告變數(@EmployeeID )、設定變數型態與值
接下來就是呼叫它啦 ~ 有沒有很簡單
真是讓人太開心了！

以下程式請參考 ~

當欄位型態為 text 時空字串如何判斷

一般而言，判斷文字型態的值是否為空字串是使用 [欄位] = ''

但這種用法在欄位型態為 text 或 ntext 時會報錯

故可改用 DATALENGTH([欄位]) = 0 去判斷

SQL 取得異動資料列數

ExecuteNonQuery 方法只會返回整數值來表示成功或受影響的資料列數目。
所以要計算實際影響的資料列時，只要將ExecuteNonQuery 方法轉換成數值來計算就好了。

畢竟有時候會寫一些IF NOT EXISTS之類的指令，實在懶得先查詢要影響的列數，就可以使用這個方式直接計算就好了！


Dim intReturn As Integer = Convert.ToInt16(myCommand.ExecuteNonQuery())

If intReturn > 0 Then
         counter += 1
End If