Go homepage(回首页) Upload pictures (上传图片) Write articles (发文字帖)
The author:(作者)归海一刀published in(发表于) 2014/2/1 0:23:04 用数据库生成不重复的流水号_[SQL,Server教程]
'*************************************************'函数名:getMaxOrder'作 用:得到最大序列号'参 数:fieldName ----在序列号表中的字段名' tableName ----序列号所在表名' fieldName ----在表中的字段名'返回值:字段的最大值'调用函数:idAdd:作用见上'*************************************************function getMaxOrder(fieldName,tableName,tableFileName) dim orderNO,orderRS,testRS set testRS=Server.CreateObject("ADODB.recordset") set orderRS=Server.CreateObject("ADODB.recordset") firstNO=year(date)&right(("0"&month(date)),2) orderSQL="select * from fieldMaxValue where fieldName='"&fieldName&"'" orderRS.open orderSQL,conn,3,2 if not orderRS.eof then orderRS("fieldMaxValue")=orderRS("fieldMaxValue") orderNO=orderRS("fieldMaxValue") if left(orderNO,6)=firstNO then orderNO=idAdd(orderNO) else orderNO=firstNO&"00001" end if else orderRS.addnew orderRS("fieldName")=fieldName orderNO=firstNO&"00001" end if testRS.open "select max("&tableFileName&") from "&tableName&" where "&tableFileName&" like '"&firstNO&"%'",conn,1,2 if (not testRS.eof) and testRS(0).value>orderNO then orderNO=idAdd(testRS(0).value) end if testRS.close set testRS=nothing orderRS("fieldMaxValue")=orderNO orderRS.update orderRS.close set orderRS=nothing getMaxOrder=orderNOend function
'*************************************************'函数名:idAdd'作 用:用来增加一:比如idAdd("5")="6",idAdd("L99")="M00",idAdd("!")="!1"' 如果是数字就到9后进位,如果是小写字母到期z后进位' 如果是大写字母到Z后进位,其它在后面加一个1 '参 数:id ----需来增加的数'返回值:增加后的数'调用函数:addOne 一个数增加一 AddOne("5")="6",Add(9)="0",AddOne("a")="b",' AddOne("z")="a",AddOne("A")="B",AddOne("Z")="A"'*************************************************Function AddOne(first)Dim tempfirstAddOne = firstintfirst = Asc(first)If (intfirst >= 48 And intfirst < 57) Or (intfirst >= 65 And intfirst < 90) Or (intfirst >= 97 And intfirst < 122) Then AddOne = Chr(intfirst + 1) Exit FunctionEnd IfIf (intfirst = 57) Then AddOne = "0" Exit FunctionEnd IfIf (intfirst = 90) Then AddOne = "A" Exit FunctionEnd IfIf (intfirst = 122) Then AddOne = "a" Exit FunctionEnd IfEnd Function
Function idAdd(id)Dim fornt, back, strFind, strBackFind, idLen, tempidif id="" or isNull(id) then iddAdd=1 exit Functionend iftempid = ididLen = Len(id)For i = 1 To idLen fornt = Left(id, idLen - i) back = Right(id, i - 1) strFind = Mid(id, idLen + 1 - i, 1) strBackFind = AddOne(strFind) id = fornt & strBackFind & back If strFind < strBackFind Then Exit For End If If strFind > strBackFind Then If i = idLen Then id = id & "1" Else If Mid(id, idLen - i, 1) = AddOne(Mid(id, idLen - i, 1)) Then id = fornt & strBackFind & back & "1" Exit For End If End If End IfNextIf id = tempid Then id = id & "1"End IfidAdd = idEnd Function
'调用示例serviceNO=getMaxOrder("serviceNO","service","serviceNO")
'其中保存所有字段流水号最大值的表的结构为:表名:fieldMaxValue字段id fieldName fieldMaxValue
赞