请教sql自动编号的问题(200分)

各位大哥大姐请帮帮小弟吧...问题是这样的：<br>&nbsp; &nbsp; 编号的前六位是固定的中间四位是年号后六位是用流水号，但是年号改变后六位的流水号自动从1开始增加，比如2007年的登记字号是4415812007000006，2008年后登记字号的后六位自动从4415812008000001。这个问题已经捆扰我好几天了请大家帮帮忙，谢谢了。

弄个维护表,把最后一个编号保存下来,然后对比判断该生成什么号码,用完了,用最后一个编号去更新维护表中的编号

比较笨的方法：<br>取当前的年，在copy最后一个编号的年，if 相等 then +1 else =1

给你一个我写的例子，跟你比较类似，但我的是日期变了流水号就自动从1开始增加。<br><br>CREATE FUNCTION getSellBillID (@Date datetime)<br>RETURNS varchar(15) AS &nbsp;<br>BEGIN <br> declare @BillID varchar(15), @BillOther varchar(12), @BillNumInt int<br> set @BillID=isnull((select top 1 smBillID from T_C_SellMain where convert(varchar(10), smDate, 120)=convert(varchar(10), @Date, 120) order by smBillID desc),'')<br> if (@BillID='') or (right(left(@BillID,11),8)&lt;&gt;cast(year(@Date) as varchar)+right('0'+cast(month(@Date) as varchar),2)+right('0'+cast(day(@Date) as varchar),2))<br> set @BillID='XS-'+cast(year(@Date) as varchar)+right('0'+cast(month(@Date) as varchar),2)+right('0'+cast(day(@Date) as varchar),2)+'-001'<br> else<br> begin<br> set @BillNumInt=cast(right(@BillID, 3) as int)+1<br> set @BillOther=left(@BillID, 12)<br> set @BillID=@BillOther+right('00'+cast(@BillNumInt as varchar),3)<br> end<br> return @BillID<br>END

编写一个存储过程！再编一个函数就行了！

呵呵。和我以前涉及到的编号差不多的模式。我是过了1天编号就好重1开始，例子<br>CREATE PROCEDURE &nbsp;WLS_Sale <br>@machinid varchar(2), <br>@salenumid int,<br>@salename varchar(30), <br>@saleprice money, <br>@salepreprice money, &nbsp;<br>@saleFlag varchar(20),<br>@Ticket_Count int,<br>@repeatprn int,<br>@disableticket bit,<br>@operid &nbsp;varchar(3), <br>@opername varchar(20),<br>@saledatetime datetime &nbsp;output,<br>@saleid &nbsp;varchar(6) output <br>&nbsp;AS<br>declare<br>@i int,@diffday int, /*@i 存记录数，@diffday存间隔人数*/<br>@datelast datetime, /*库中最后的日期 set @i=cast(@maxid as int)*/<br>@maxid varchar(6),@OldTicket_Count int /*当天最后记录的ID*/<br><br>select @datelast=max(Sale_Datetime) from &nbsp;day_SaleRecords /*取库中最后的日期*/<br>set @diffday=datediff(Day,@datelast,@saledatetime)<br><br>select @i=count(*) from day_SaleRecords where datediff(Day,@datelast,Sale_Datetime)=0/*是否存在记录*/<br><br>if (@i=0) or (@diffday&gt;0) &nbsp; <br>&nbsp; begin<br>&nbsp; &nbsp; set @saleid='000001' <br>&nbsp; end<br>&nbsp;else<br>&nbsp; &nbsp;begin &nbsp;/*计算当天每笔零售票号*/<br>&nbsp; &nbsp; &nbsp;select @maxid=max(Sale_ID) from day_SaleRecords where datediff(Day,@datelast,Sale_Datetime)=0 /*取当天最后记录的ID*/<br>&nbsp; &nbsp; &nbsp;select @OldTicket_Count=Ticket_Count from day_SaleRecords where sale_id=(select max(sale_id) from day_salerecords where datediff(Day,@datelast,Sale_Datetime)=0) and datediff(Day,@datelast,Sale_Datetime)=0<br>&nbsp; &nbsp; &nbsp;set @saleid=right('000000'+ltrim(str(cast(@maxid as int)+@OldTicket_Count,6,0)),6) /*生成新ID*/<br>&nbsp; &nbsp;end<br>set @saledatetime=getdate() &nbsp;/*当前日期时间*/<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; /*日期时间，机器ID，套票号，原价，套票名称，优惠价，重打次，是否退票，操作员ID，操作员名）*/<br>insert LS_SaleRecords values(@saledatetime,@machinid,@saleid,@salenumid,@salename,@saleprice,@salepreprice,@saleFlag,@Ticket_Count,@repeatprn,@disableticket,@operid ,@opername)<br>insert Day_SaleRecords values(@saledatetime,@machinid,@saleid,@salenumid,@salename,@saleprice,@salepreprice,@saleFlag,@Ticket_Count,@repeatprn,@disableticket,@operid ,@opername)<br>----------------------------------------<br>GO

Update table set sn=case when substring(max(sn),7,4)=cast(DATEPART(yyyy, getdate()) as varchar) then &nbsp;max(sn)+1 else '441581'+cast(DATEPART(yyyy, getdate()) as varchar) +'000001' end<br><br>语法或类型转换不一定对， 自己再调整吧

zgj_gd的方法不错<br>用case......不错，以后试试

谢谢大家无私的帮助。

谢谢

Update table set sn=case when substring(max(sn),7,4)=cast(DATEPART(yyyy, getdate()) as varchar) then &nbsp;max(sn)+1 else '441581'+cast(DATEPART(yyyy, getdate()) as varchar) +'000001' end<br><br>显示聚合不应出现在 UPDATE 语句的集合列表中。