ex: 流水序號(限integer編碼)
0 1 2 5 6 8 12 下一次 補上 3(最小號缺號) or 補上 11(最大號缺號)
0 1 2 3 4 5 最大(或最小) 都會補6
解決：
找出缺號中的最大值
select max(staff_sn-1) as lostnum from employee where (not ((staff_sn-1) in (select staff_sn from employee)))
select max(序號)-1 from [A010詢價單資料表-備註] a
where not exists(select 1 from [A010詢價單資料表-備註] where 序號=a.序號-1 and 詢價單號 = 'AA01') and 詢價單號 = 'AA01';
select top 1 t1.序號-1 from [A010詢價單資料表-備註] t1
where not exists ( select 1 from [A010詢價單資料表-備註] t2 where t2.序號 = t1.序號 -1 and t2.詢價單號 = 'AA01' ) and t1. 詢價單號 = 'AA01'
order by t1.序號 DESC
注意：(有待改進)
若是 5 6 7 則補號為 4 而不是 8 -- 序號間無缺號
若是 1 2 3 則補號為 0
若是 0 1 2 則補號為 -1
-------------------------------
找出缺號中的最小值
select min(staff_sn+1) as lostnum from employee where (not ((staff_sn+1) in (select staff_sn from employee)))
select max(序號)+1 from [A010詢價單資料表-備註] a
where not exists(select 1 from [A010詢價單資料表-備註] where 序號=a.序號+1 and 詢價單號 = 'AA01') and 詢價單號 = 'AA01';
select top 1 t1.序號+1 from [A010詢價單資料表-備註] t1
where not exists ( select 1 from [A010詢價單資料表-備註] t2 where t2.序號 = t1.序號 +1 and t2.詢價單號 = 'AA01' ) and t1. 詢價單號 = 'AA01'
order by t1.序號
注意：(有待改進)
若是 3 6 7 則補號為 4 而不是 1
若是 5 6 7 則補號為 8 而不是 1 -- 序號間無缺號
-------------------------------
正式應用：
insert into [Usys使用者資料表] (使用者ID) select min(使用者ID+1) from [Usys使用者資料表] where (not ((使用者ID+1) in (select 使用者ID from [Usys使用者資料表])))
sqlQuery = "insert into [Usys使用者資料表] (使用者ID,帳號,密碼,使用者名稱,Email,CreateDate,CreateUserID,CreateUserName," & _
"ModifyDate,ModifyUserID,ModifyUserName,SystemModifyDate)" & _
" select min(使用者ID+1),@帳號,@密碼,@使用者名稱,@Email,@建檔日,@建檔人ID,@建檔人," & _
"@修檔日,@修檔人ID,@修檔人,@系統修檔日 from [Usys使用者資料表]" & _
" where (not ((使用者ID+1) in (select 使用者ID from [Usys使用者資料表])));"
另一種insert的正式應用：
'找出 使用者 在此系統別下 所缺的程式...
'select 程式ID from Usys系統別程式資料表 where 系統ID = 1 and 程式ID not in (select 程式ID from Usys使用者程式資料表 where 使用者ID = 5);
'找將找出的程式record (多筆) 一筆一筆新增到Usys使用者程式資料表
Dim sqlQuery_Progs As String = "insert into [Usys使用者程式資料表] (使用者ID,程式ID) select " & DataGridView1.CurrentRow.Cells(0).Value & ", 程式ID from Usys系統別程式資料表 where 系統ID = " & Microsoft.VisualBasic.Left(frmEdit.ComboBox1.Text, InStr(frmEdit.ComboBox1.Text, "(") - 1) & " and 程式ID not in (select 程式ID from Usys使用者程式資料表 where 使用者ID = " & DataGridView1.CurrentRow.Cells(0).Value & ")"
',權限,執行權限,新增權限,修改權限,刪除權限,管理權限 DB已經有設置預設值都是 0
(承上)用另種方式來新增insert：
找出系統ID下的所有程式ID(Usys系統別程式資料表) 之後新增給 Usys使用者程式資料表 如果要新增的這個程式ID 並不存在於 Usys使用者程式資料表(即原本就存在就 不需再新增)
insert into Usys使用者程式資料表 (使用者ID,程式ID) select 5,程式ID from Usys系統別程式資料表 Where 系統ID = 1 and not exists (select * from Usys使用者程式資料表 where Usys系統別程式資料表.程式ID = Usys使用者程式資料表.程式ID)
缺點：
若無序號列存在，即無缺號被找到...
固也無法新增... (因為是null) insert 失敗!!
所以在新增前必須判斷缺號 count(id) 是否>0
若count(id) = 0 則 insert 時的序號 直接給定 1(sql字串 要換掉)
解決：沒有資料列時 則補1
select case when min(序號+1) is NULL then 1 else min(序號+1) end as lostnum from [A010詢價單資料表-備註] where (not ((序號+1) in (select 序號 from [A010詢價單資料表-備註] where 詢價單號 = 'AA01'))) and 詢價單號 = 'AA01';
最理想的解決--找出最小值：(等待神的出現....)
沒有資料列時 則補1
有資料列時 5 7 8 9 則補1 而不是 補6
有資料列時 6 7 8 9 則補1 而不是 補10(或4)
select & insert
insert into [tableDetail] (報表編號, 內容, 建檔日, 建檔人, 建檔人ID, 修檔日, 修檔人, 修檔人ID,系統修檔日)
select 報表編號, 瑕疵原因, 建檔日, 建檔人, 建檔人ID, 修改日, 修改人, 修改人ID,系統修改日 from [tableMain];
另一篇：
UPDATE & SELECT
參考：
藍色小惡魔討論區: SQL
SQL 自製流水號做法(有規律的序號)
SQL-自動補流水號
新增資料時自動產生識別代號的一些方法
获取自动编号的问题(经典实用)
返回已用编号、缺号分布字符串的处理示例
融合了补号处理的编号生成处理示例
SQL语句处理流水号编号补号
流水號自動補號和計算所缺最小號和最大號
自动生成序号的存储过程
编号连续不能断号，断号后补号
[SQL] 流水號跳號
DB建置流水號問題
如何找出缺少的單據編號？能否用一個Select語句實現？
請問如何設計查詢找出缺的單號
数据库 查询缺号列出缺号的所有号码
UPDATE OR INSERT in one statement
SELECT INTO 和 INSERT INTO SELECT 两种表复制语句
第二種 INSERT INTO 能夠讓我們一次輸入多筆的資料。
使用 INSERT 與 SELECT 加入資料列
一次新增多筆資料 － INSERT ... SELECT
如何找到資料表某欄位第一個缺號的數值
SELECT INTO 和 INSERT INTO SELECT 區別
使用 INSERT 與 SELECT 加入資料列
避免自動增量 衝突pt1
避免自動增量 衝突pt2
一列数据存了一组不连续的正整数，求一SQL查询空缺的最小的值
Oracle层次查询和分析函数在号段选取中的应用
用VBA解決 自動編號的主KEY insert缺號
SQL::CASE, NULLIF() and ISNULL()
ISNULL (Transact-SQL)
NULLIF (Transact-SQL)
SQL - 使用 NULLIF
SQLServer 中的 ISNULL 和 NULLIF
SQL SERVER – Explanation and Comparison of NULLIF and ISNULL
沒有留言:
張貼留言