经典模式

简洁实用,快捷灵活

当前位置:首页>平台开发>经典模式
全部 1208 平台特色 8 经典模式 77 流行模式 108 技术论坛 1014 经典视频 1

自动编号实现集锦

时间:2022-05-07   访问量:28118

为方便用户,本站提供了单机版、CS局域网版、CS互联网版及BS(互联网版)四个版本,以适应用户的不同求,下载后安装时分别可以选择需要的版本(单机版、CS互联网版及BS(互联网版))进行安装试用。



1、录入窗口背景图形去掉,用标准的颜色。

2、录入窗口中显示照片的控件尽量选择自动大小。

3、用webdbimp.exe打开信息库,录入窗口整体的高度和宽度比例尽量协调。

√×√ 

【 】 



编辑格式:!9999/99/00 99:99;1;

显示格式:dddddd hh时nn分



{[(主表).设计任务7]}


where year(日期) + '-'+right('0' + month(日期),2) + '-'+right('0' + day(日期),2)=year(getdate()) + '-'+right('0' + month(getdate()),2) + '-'+right('0' + day(getdate()),2)



where 入院日期 between ':起始日期' and ':截止日期'


ftp://inmisdown@www.hdcsc.com/other/MSDE/MSDESP4.rar

sa

msde




':起始日期' as 起始日期,':截止日期' as 截止日期


 ':起始日期' AND ':截止日期'



select datediff('d',#2007-08-07#,date()) as 天数,int(datediff('d',#2007-08-07#,date())/28) as 月数,datediff('w',#2007-08-07#,date()) as 周数

,datediff('d',#2007-08-07#,date())-datediff('w',#2007-08-07#,date())*7 as 周天数 

=====

select datediff('d',#2007-08-07#,date()) as 天数,int(datediff('d',#2007-08-07#,date())/28) as 月数,datediff('w',#2007-08-07#,date()) as 周数

,datediff('d',#2007-08-07#,date())-datediff('w',#2007-08-07#,date())*7 as 周天数,datediff('m',#2007-08-07#,date()) as 月数2,

datediff('d',dateadd('m',datediff('m',#2007-08-07#,date()),#2007-08-07#),date()) as 月数几天




一、有前缀(单机版):


SELECT 'PR'& year(#:入库日期#) & right('0' & month(#:入库日期#),2) & right('0' & day(#:入库日期#),2) & iif(A.ID is null,'001',right('000' & A.ID,3)) AS 单号

FROM (SELECT max(int(right(配件入库单.入库单号,3))) +1 as ID FROM 配件入库单 where 入库日期=#:入库日期#) AS A


或:

SELECT year(date())  & right('0' & month(date()),2) & right('0' & day(date()),2) & iif(A.ID is null,'0001',right('0000' & A.ID,4)) AS 单号

FROM (SELECT right(max(门诊收费表.流水帐号),4) +1 as ID FROM  门诊收费表 where left(门诊收费表.流水帐号,8)=year(date()) & right('0' & month(date()),2) & right('0' & day(date()),2)) AS A


或:

SELECT 'SBJY' & right(year(date()),2) & iif(A.ID is null,'0001',right('0000' & A.ID,4)) AS 编号

FROM (SELECT max(int(right(设备借用记录.借用单编号,4))) +1 as ID FROM 设备借用记录 where left(借用单编号,6)='SBJY' & right(year(date()),2)) AS A



有前缀(网络版:)

SELECT 'BJ'+cast(year(':报价日期') as varchar(4))+cast(month(':报价日期') as varchar(2))+cast(day(':报价日期') as varchar(2))+right(str(1000 + (case 

when  A.ID is null then '1' 

else A.ID

end)),3) AS 单号

FROM (SELECT cast(right(max(报价单.报价单号),3) as int)+1 as ID FROM 报价单 where 报价日期=':报价日期') AS A



SELECT 'G' + cast(year(getdate()) as varchar(4))+right(100 + month(getdate()),2)+right(100 + day(getdate()),2)+right(str(10000 + (case 

when  A.ID is null then '1' 

else A.ID

end)),4) AS 单号

FROM (SELECT cast(right(max(设备申请单.申请单号),4) as int)+1 as ID FROM 设备申请单 where right(left(申请单号,9),8)= cast(year(getdate()) as varchar(4)) + right('0' + cast(month(getdate()) as varchar(2)),2)+ right('0' + cast(day(getdate()) as varchar(2)),2)) AS A

同:

SELECT 'RK' + cast(year(getdate()) as varchar(4))+right(100 + month(getdate()),2)+right(100 + 

day(getdate()),2)+right(str(1000 + (case 

when  A.ID is null then '001' 

else A.ID

end)),3) AS 入库单号

FROM (SELECT cast(right(max(空调入库单.入库单号),3) as int)+1 as ID FROM 空调入库单

where left(入库单号,8)= cast(year(getdate()) as varchar(4)) + right('0' + cast(month(getdate()) as 

varchar(2)),2)+ right('0' + cast(day(getdate()) as varchar(2)),2)) AS A






SELECT right(str(100000 + (case 

when  A.ID is null then '1' 

else A.ID

end)),5) AS 单号

FROM (SELECT cast(right(max(设备年度技措计划表.技措编号),5) as int)+1 as ID FROM 设备年度技措计划表) AS A



网络版:(系统日期)自动编号:(位数一样)


SELECT cast(year(getdate()) as varchar(4))+right(100 + month(getdate()),2)+right(100 + 

day(getdate()),2)+right(str(10000 + (case 

when  A.ID is null then '1' 

else A.ID

end)),4) AS 单号

FROM (SELECT cast(right(max(安装记录表.单号),5) as int)+1 as ID FROM 安装记录表 where left(单号,8)= cast(year(getdate()) as varchar(4)) + right('0' + cast(month(getdate()) as 

varchar(2)),2)+ right('0' + cast(day(getdate()) as varchar(2)),2)) AS A



单机版:

SELECT year(date()) & iif(A.ID is null,'0000001',right('0000000' & A.ID,7)) AS 单号

FROM (SELECT right(max(订货合同.NO),7) +1 as ID FROM  X订货合同 where left(订货合同.NO,4)=YEAR(DATE())) AS A

===============

SELECT year(date()) as 年度,iif(A.ID is null,'000001',right('000000' & A.ID,6)) AS 号

FROM (SELECT right(max(重大案件协查书.号),6) +1 as ID FROM 重大案件协查书 where 第=YEAR(DATE())) AS A



网络版:从1开始,无条件:

SELECT ltrim(case 

when  A.ID is null then '1' 

else right('0000000' & A.ID,10)

end) AS 单号

FROM (SELECT max(cast(设备年度技措计划表.技措编号 as int) )+1 as ID FROM 设备年度技措计划表) as A


------------


单机版:

 常    用:   SELECT  year(#:领料日期#) & right('0' & month(#:领料日期#),2) & right('0' & day(#:领料日期#),2) & iif(A.ID is null,'001',right('000' & A.ID,3)) AS 领料单号


FROM (SELECT int(right(MAX(维护领料.领料单号),3)+1) AS ID  FROM 维护领料 where 领料日期=#:领料日期#) AS A 


 以系统时间: SELECT ( year(date()) & right('0' & month(date()),2) & right('0' & day(date()),2) & right(int(100 + ("01" & A.ID)),2)) AS 序号

FROM (SELECT MAX(right(int(100 + 部件完工主表.序号),3)+1) AS ID  FROM 部件完工主表 where left(序号,8)=year(date()) & right('0'&month(date()),2) & right('0' & day(date()),2)) AS A


 加前缀:     SELECT 'HY' & (year(#:登记日期#) & right('0' & month(#:登记日期#),2) & right('0' & day(#:登记日期#),2) & right(str(100 + ("01" & A.ID)),2)) AS 客户编号 FROM (SELECT int(right(MAX(户外广告.客户编号),4))+1 AS ID  FROM 户外广告 WHERE 登记日期=#:登记日期#) AS A


从0000001开始:SELECT  right(str((10000000 + ("0000000" & A.ID))),7) AS 合同号

FROM (SELECT MAX(right(str((10000000 + 合同主表.合同号)),7))+1 AS ID  FROM 合同主表) AS A


 日期+字段:SELECT (year(#:进货日期#) & right('0' & month(#:进货日期#),2) & right('0' & day(#:进货日期#),2) & (":车号(船号)")) AS 编号


 中间加“-”型的:SELECT ( year(date()) & right('0' & month(date()),2) & right('0' & day(date()),2) & "-" & right(str(1000 + ("000" & A.ID)),3)) AS 编号

FROM (SELECT int(right(max(托运货物登记表.编号),3)+1) AS ID  FROM 托运货物登记表 WHERE year(登记时间)=year(date()) and month(登记时间)=month(date()) and day(登记时间)=day(date())) AS A


12、SELECT ( ':船名' & right( year(date()),2) & right(int(1000 + ("000" & A.ID)),3)) AS 编号

FROM (SELECT MAX(right((航修费用.编号),3)+1) AS ID  FROM 航修费用 where 船名=':船名') AS A 



网络版:


 常用:SELECT cast(year(':申请时间') as varchar(4))+ right('0'+cast(month(':申请时间') as varchar(2)),2)+right('0'+cast(day(':申请时间') as varchar(2)),2) +right(str((cast(100 as varchar(3))+ cast('00' as varchar(2))+ (case 

when  A.ID is null then '1' 

else A.ID

end) )),2) AS 申请编号

FROM (SELECT cast(right(max(用车申请.申请编号),3) as int)+1 as ID FROM 用车申请 where 申请时间 =':申请时间') AS A


 具体时间的:SELECT cast(year(':派单时间') as varchar(4))+ right('0'+cast(month(':派单时间') as varchar(2)),2)+right('0'+cast(day(':派单时间') as varchar(2)),2) +right(str((cast(100 as varchar(3))+ cast('00' as varchar(2))+ (case 

when  A.ID is null then '1' 

else A.ID

end) )),2) 派工单号

FROM (SELECT cast(right(max(派工单.派工单号),6) as int)+1 as ID FROM 派工单 where left(派工单号,8) =cast(year(':派单时间') as varchar(4))+ right('0'+cast(month(':派单时间') as varchar(2)),2)+right('0'+cast(day(':派单时间') as varchar(2)),2)) AS A


 加前缀的:A、SELECT ':分类代码'+cast(year(':立案日期') as varchar(4))+right(str(100000 + (case 

when  A.ID is null then '1' 

else A.ID

end)),4) AS 案件编号

FROM (SELECT cast(right(max(编号.案件编号),4) as int)+1 as ID FROM 编号 where 案件分类=':案件分类') AS A


B、SELECT  'P' + cast(year(getdate()) as varchar(4)) + right(str((cast(1000000 as varchar(7))+ (case 

when  A.ID is null then '1' 

else A.ID

end) )),6) AS 派车单号

FROM (SELECT cast(right(max(车辆调度.派车单号),6) as int)+1 as ID FROM 车辆调度) AS A


 系统时间:SELECT cast(year(getdate()) as varchar(4)) +right('0'+cast(month(getdate()) as varchar(2)),2)

+right('0'+cast(day(getdate()) as varchar(2)),2)+ right(str((cast(1000000 as varchar(7))+ (case 

when  A.ID is null then '1' 

else A.ID

end) )),6) AS 信信访件编号

FROM (SELECT cast(right(max(总表.信信访件编号),6) as int)+1 as ID FROM 总表) AS A


自动编号从1开始到无数

单机版:SELECT  right(str((1000 + ("000" & A.ID))),3) AS 序号

FROM (SELECT MAX(right(str((1000 + 供应商信息.序号)),4))+1 AS ID  FROM 供应商信息) AS A


网络版:SELECT ltrim(case 


when  A.ID is null then '1' 


else (A.ID)


end) AS 序号 


FROM (SELECT max(cast(保险费用.序号 as int) )+1 as ID FROM 保险费用) as A  



SELECT ':出车类别'+cast(year(':日期') as varchar(4))+ right('0'+cast(month(':日期') as varchar(2)),2)+right('0'+cast(day(':日期') as varchar(2)),2) +right(str((cast(100 as varchar(3))+ cast('00' as varchar(2))+ (case 

when  A.ID is null then '1' 

else A.ID

end) )),2) AS 出车单号

FROM (SELECT cast(right(max(班车登记.出车单号),3) as int)+1 as ID FROM 班车登记 where 日期 =':日期' and 出车类别=':出车类别') AS A



8、select date() &' ' & time() as 日期

单机版

select getdate() as 日期

网络版

只取日期:

SELECT cast(year(getdate()) as varchar(4)) +'-'+right('0'+cast(month(getdate()) as varchar(2)),2)

+'-'+right('0'+cast(day(getdate()) as varchar(2)),2) as 日期



------------------------------------------


SELECT cast(year(getdate()) as varchar(4))+right(100 + month(getdate()),2)+right(100 + 

day(getdate()),2)+right(str(10000 + (case 

when  A.ID is null then '1' 

else A.ID

end)),4) AS 单号

FROM (SELECT cast(right(max(安装记录表.单号),5) as int)+1 as ID FROM 安装记录表 where left(单号,8)= cast(year(getdate()) as varchar(4)) + right('0' + cast(month(getdate()) as 

varchar(2)),2)+ right('0' + cast(day(getdate()) as varchar(2)),2)) AS A 



---------------------------------------


自动日期+编号


SELECT  year(#:入库时间#) & right('0' & month(#:入库时间#),2) & right('0' & day(#:入库时间#),2) & (right(val((1000 + ("001" & A.ID))),3)) AS 入库单号 FROM (SELECT MAX(入库.入库单号)+1 AS ID  FROM 入库 where 入库时间=#:入库时间#) AS A 




(这个是系统时间加上后四位数,即20060001)

SELECT year(date()) & (right(val((10000 + ("0001" & A.ID))),4)) AS 人员编号


FROM (SELECT MAX(戒毒人员信息档案.人员编号)+1 AS ID FROM 戒毒人员信息档案 ) AS A 


上一篇:单机版和网络版SQL函数的一些比较

下一篇:没有了!

发表评论:

评论记录:

未查询到任何数据!

在线咨询

点击这里给我发消息 售前咨询专员

点击这里给我发消息 售后服务专员

在线咨询

免费通话

24小时免费咨询

请输入您的联系电话,座机请加区号

免费通话

微信扫一扫

微信联系
返回顶部
备案号码:鲁ICP备09000001号-2