ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[Âà¶K] SQL¦Û°Ê½s¸¹

[Âà¶K] SQL¦Û°Ê½s¸¹

¸ê®Æ¨Ó·½¡Ghttp://marchlifeshow.blogspot.com/

¼gµ{¦¡±`»Ý­n¥Î¨ì¬y¤ô¸¹¡A¥i¥H¥Îµ{¦¡¼g¡A¤£¹L¤ñ¸û³Â·Ð
©³¤Uªº±Ð¾Ç¬O¦bSQL¤º¼g¤@­Ó¨ç¦¡
¥H«á­n¥Î¨ì´N¥u­n©I¥s´N¥i¥H¤F~®¼¤è«Kªº~

­n¨Ï¥Î MSSQL ªº¦Û°Ê½s¸¹Äæ¦ì, ¥u»Ý­n²³æªº³]©wÃѧO³W®æ¤¤ Is Identity, ´N¥i¥H«Ü®e©öªº²£¥Í¤@­Ó¦Û°Ê½s¸¹Äæ¦ì¡C¤£¹L¦³®É­Ô³o¼Ëªº¦Û°Ê½s¸¹¨Ã¤£¯àº¡¨¬©Ò¦³ªº»Ý¨D¡C¨Ò¦p«È¤á¥i¯à·|­n¨D­q³æ½s¸¹¬O¥[¤W¤é´Á¤§Ãþªº°µ¬°­q³æªº¶}ÀY¡C¨Ò¦p 2008/11/4 ²Ä¤@µ§­q³æªº­q³æ½s¸¹­n¬O 2008110400001, ²Ä¤Gµ§´N­n¬O 2008110400002, 11/5 ªº¤S­n­« 1 ¶}©l½s°_ 2008110500001, 2008110500002...¡C
³o®É­Ô§Q¥Î¦Û°Ê½s¸¹Äæ¦ì´N¤£°÷¨Ï¥Î¤F¡C

³o®É­Ô¥i¥H§Q¥Î¨ç¼Æªº¤è¦¡¨Ó¸Ñ¨M³o­Ó°ÝÃD¡C¤U­±´Nª½±µ°µ¤@­Ó½d¨Ò¡C
­º¥ý§Ú­Ì¥ý«Ø¥ß¤@­Ó¸ê®Æªí Orders
  1. CREATE TABLE [dbo].[Orders](
  2.   [ID] [int] IDENTITY(1,1) NOT NULL,
  3.   [OrderID] [nvarchar](20),
  4.   [CustomerID] [int],
  5.   [OrderDate] [smalldatetime],
  6.   CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
  7.   (
  8.     [ID] ASC
  9.   )
  10. )
½Æ»s¥N½X
¦A¨Ó, §Ú­Ì¥ý«Ø¥ß¤@­Ó¨ç¼Æ GetOrderID():
  1. CREATE FUNCTION GetOrderID()
  2. RETURNS nvarchar(20)
  3. AS
  4. BEGIN
  5.   DECLARE @OrderID nvarchar(20)
  6.   DECLARE @DT nvarchar(20)
  7.   SELECT @DT = convert(varchar(10),getdate(),112)
  8.   SELECT @OrderID= @DT + right('0000' + ltrim(isnull(max(cast(right(OrderID, 5) as int)),0)+1), 5) from Orders where left(OrderID, 8) = @DT
  9.   RETURN @OrderID
  10. END
  11. GO
½Æ»s¥N½X
* ·s«Ø¥ßªº¨ç¼Æ, §A¥i¥H¦b¸ê®Æ®w¤Uªº¡u¥iµ{¦¡©Ê / ¨ç¼Æ / ¯Â¶q­È²[¼Æ¡v¤¤§ä¨ì
³Ì«á, ±N¸ê®Æªí»P¨ç¼Æ¾ã¦Xªº¤èªk¬O¶i¤J³]­p¸ê®Æªí¡C¦b OrderID Äæ¦ìªº¡u¹w³]­È©Îôµ²¡v¿é¤J dbo.GetOrderID()¡C¦¹®É¦b¸ê®Æªí¤¤¦A·s¼W¸ê®Æ®É, ­q³æ½s¸¹ OrderID Äæ¦ì, ´N·|²£¥Í§Ú­Ì·Q­nªº½s¸¹¤è¦¡¤F¡C

¸ê®Æ¨Ó·½¡Ghttp://marchlifeshow.blogspot.com/

        ÀR«ä¦Û¦b : ¦³®É·í«äµL®É­W¡A¦n¤Ñ­n¿n«B¨Ó³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD