iT邦幫忙

0

MS SQL Auto Increment的欄位可否依據某欄位做遞增值?

各位前輩好
我知道SQL可以設定某個欄位為AUTO INCREMENT(通常也會一併設定PK),但這個欄位可否能依據同Table內某個欄位做AUTO INCREMENT呢? 就不設定該欄位為PK了
主要依據的欄位為SN!
圖示說明如下:
https://ithelp.ithome.com.tw/upload/images/20181115/20107387kMZFgILP3y.png

謝謝~~~

1
純真的人
iT邦高手 3 級 ‧ 2018-11-15 17:17:59

可以用查詢的方式去新增..

declare @Test table(
	Test_ID int
	,Test_Txt nvarchar(5)
	,Test_Count int
)

insert into @Test
select Set_ID
,Set_Txt
,isNull((select count(*) from @Test where Set_ID >= Test_ID and Set_Txt = Test_Txt),0)+1 as Set_Count
from (
	select isNull((select max(Test_ID) from @Test),0) + 1 as Set_ID
	,'A' as Set_Txt
) as k

insert into @Test
select Set_ID
,Set_Txt
,isNull((select count(*) from @Test where Set_ID >= Test_ID and Set_Txt = Test_Txt),0)+1 as Set_Count
from (
	select isNull((select max(Test_ID) from @Test),0) + 1 as Set_ID
	,'B' as Set_Txt
) as k

insert into @Test
select Set_ID
,Set_Txt
,isNull((select count(*) from @Test where Set_ID >= Test_ID and Set_Txt = Test_Txt),0)+1 as Set_Count
from (
	select isNull((select max(Test_ID) from @Test),0) + 1 as Set_ID
	,'C' as Set_Txt
) as k

insert into @Test
select Set_ID
,Set_Txt
,isNull((select count(*) from @Test where Set_ID >= Test_ID and Set_Txt = Test_Txt),0)+1 as Set_Count
from (
	select isNull((select max(Test_ID) from @Test),0) + 1 as Set_ID
	,'B' as Set_Txt
) as k

insert into @Test
select Set_ID
,Set_Txt
,isNull((select count(*) from @Test where Set_ID >= Test_ID and Set_Txt = Test_Txt),0)+1 as Set_Count
from (
	select isNull((select max(Test_ID) from @Test),0) + 1 as Set_ID
	,'C' as Set_Txt
) as k

insert into @Test
select Set_ID
,Set_Txt
,isNull((select count(*) from @Test where Set_ID >= Test_ID and Set_Txt = Test_Txt),0)+1 as Set_Count
from (
	select isNull((select max(Test_ID) from @Test),0) + 1 as Set_ID
	,'C' as Set_Txt
) as k

select * from @Test

https://ithelp.ithome.com.tw/upload/images/20181115/20061369pWR4dz0wg9.png

rchin iT邦新手 5 級 ‧ 2018-11-16 08:48:22 檢舉

感謝,我會參考這個方法跟後端去做配合測試~~

1
浩瀚星空
iT邦研究生 3 級 ‧ 2018-11-15 17:50:03

一般來說是不太可能直接這樣用。
只能利用後端程式,來寫程式處理。或是sql的事件功能函式來幫你處理了。
當然用「純真的人」給你用的方式也是一種方法。

其實這方法只是利用了sql來寫程式處理。也算是一招就是了。

rchin iT邦新手 5 級 ‧ 2018-11-16 08:50:09 檢舉

瞭解! 本來以為資料庫若能直接這樣設定會省事很多

1
sam0407
iT邦高手 1 級 ‧ 2018-11-16 16:29:05

我的作法是這樣:
1.先建一個函數GetSNCount(假設Table Name是dbo.test)

CREATE FUNCTION [dbo].[GetSNCount](@SN varchar(10))  

RETURNS  int
AS
BEGIN
	Declare @SNCount int = 0
	SELECT @SNCount=count(SN) from dbo.test where SN=@SN group by SN

	set @SNCount = @SNCount + 1
	-- Return the result of the function
	RETURN @SNCount

END

2.Insert測試資料

insert test(sn,[count]) values('A',dbo.getSNCount('A'))
insert test(sn,[count]) values('B',dbo.getSNCount('B'))
insert test(sn,[count]) values('C',dbo.getSNCount('C'))
insert test(sn,[count]) values('B',dbo.getSNCount('B'))
insert test(sn,[count]) values('C',dbo.getSNCount('C'))
insert test(sn,[count]) values('C',dbo.getSNCount('C'))

3.結果如下
https://ithelp.ithome.com.tw/upload/images/20181116/20012665etYfkVfdrv.png

我要發表回答

立即登入回答