albert0956的部落格

SQL 自動編號進階設計
 
要使用 MSSQL 的自動編號欄位, 只需要簡單的設定識別規格中 Is Identity, 就可以很容易的產生一個自動編號欄位。不過有時候這樣的自動編號並不能滿足所有的需求。例如客戶可能會要求訂單編號是加上日期之類的做為訂單的開頭。例如 2008/11/4 第一筆訂單的訂單編號要是 2008110400001, 第二筆就要是 2008110400002, 11/5 的又要重 1 開始編起 2008110500001, 2008110500002...。
這時候利用自動編號欄位就不夠使用了。

這時候可以利用函數的方式來解決這個問題。下面就直接做一個範例。
首先我們先建立一個資料表 Orders

CREATE TABLE [dbo].[Orders](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [OrderID] [nvarchar](20),
  [CustomerID] [int],
  [OrderDate] [smalldatetime],
  CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
  (
    [ID] ASC
  )
)

再來, 我們先建立一個函數 GetOrderID():

CREATE FUNCTION GetOrderID()
RETURNS nvarchar(20)
AS
BEGIN
  DECLARE @OrderID nvarchar(20)
  DECLARE @DT nvarchar(20)
  SELECT @DT = convert(varchar(10),getdate(),112)
  SELECT @OrderID=
                    @DT + right('0000'
                                     + ltrim( 
                                           isnull(max(cast(right(OrderID, 5) as int)),0)
                                     +1)
                               , 5) from Orders where left(OrderID, 8) = @DT
  RETURN @OrderID
END
GO

* 新建立的函數, 你可以在資料庫下的「可程式性 / 函數 / 純量值涵數」中找到

最後, 將資料表與函數整合的方法是進入設計資料表。在 OrderID 欄位的「預設值或繫結」輸入 dbo.GetOrderID()。此時在資料表中再新增資料時, 訂單編號 OrderID 欄位, 就會產生我們想要的編號方式了。

1 2008110300001 5 2008/11/4 下午 05:12:00
2 2008110300002 1 2008/11/4 下午 05:12:00
3 2008110300003 3 2008/11/4 下午 05:12:00
4 2008110300004 4 2008/11/4 下午 05:12:00
5 2008110400001 2 2008/11/4 下午 05:35:00
6 2008110400002 5 2008/11/4 下午 05:35:00
7 2008110400003 4 2008/11/4 下午 05:35:00