select isnull((select top 1 right(编号,3) from tb order by cast(right(编号,3) as int) desc),'000')
select right(col,3) from tb where col like '%'+right(convert(varchar,getdate(),112),6)+'[0-9][09-][0-9]' order by right(col,3) desc
select isnull((select top 1 right(编号,3) from tb where datediff(dd,日期字段,getdate())=0 order by cast(right(编号,3) as int) desc),'000')
select isnull(select right(编号,3) from tb order by cast(right(编号,3) as int) desc),'000')
SQL code select isnull((select top 1 right(编号,3) from tb order by cast(right(编号,3) as int) desc),'000') from table where 编号 like '%'+Select CONVERT(varchar(100), GETDATE(), 12)+'%'
假设你的表是TB,这一列叫COL1 SELECT ISNULL( ( SELECT MAX(RIGHT(COL1,3)) FROM TB WHERE SUBSTRING(COL1,5,6)=RIGHT(CONVERT(VARCHAR(10),GETDATE(),112),6) ),'000')
我的写法里的WHERE条件就是这么判断的。如果列上有索引,不会慢。
select isnull( (select top 1 right(编号,3) from tb where datediff(dd,substring(编号,5,6),getdate())=0 --日期为今天的 order by cast(right(编号,3) as int) desc ), '000' )
create table lin2(车辆信息 varchar(100)) insert into lin2 select 'YCGK100817001' insert into lin2 select 'YCGK100817002' insert into lin2 select 'YCGK100817003' insert into lin2 select 'YCGK100822004' insert into lin2 select 'YCGK100822002' insert into lin2 select 'YCGK100817004' insert into lin2 select 'YCGK100817005' select 车辆信息=isnull(max(right(车辆信息,3)),000) from lin2 where substring(车辆信息,5,6)=substring(convert(varchar(8),getdate(),112),3,6)go drop table lin2
SELECT @STR='YCGK100817001'
SELECT ISNULL(
(
SELECT RIGHT(@STR,3)
WHERE SUBSTRING(@STR,5,6)=RIGHT(CONVERT(VARCHAR(10),GETDATE(),112),6)
)
,'000')
--001
isnull((select top 1 right(编号,3) from tb order by cast(right(编号,3) as int) desc),'000')
order by right(col,3) desc
isnull((select top 1 right(编号,3) from tb where datediff(dd,日期字段,getdate())=0 order by cast(right(编号,3) as int) desc),'000')
order by cast(right(编号,3) as int) desc),'000')
SQL code
select
isnull((select top 1 right(编号,3) from tb order by cast(right(编号,3) as int) desc),'000') from table where 编号 like '%'+Select CONVERT(varchar(100), GETDATE(), 12)+'%'
SELECT ISNULL(
(
SELECT MAX(RIGHT(COL1,3))
FROM TB
WHERE SUBSTRING(COL1,5,6)=RIGHT(CONVERT(VARCHAR(10),GETDATE(),112),6)
),'000')
isnull(
(select top 1 right(编号,3)
from tb
where datediff(dd,substring(编号,5,6),getdate())=0 --日期为今天的
order by cast(right(编号,3) as int) desc
),
'000'
)
另外 楼上的 varchar 最大是8000 nvarchar 最多才是4000
insert into lin2 select 'YCGK100817001'
insert into lin2 select 'YCGK100817002'
insert into lin2 select 'YCGK100817003'
insert into lin2 select 'YCGK100822004'
insert into lin2 select 'YCGK100822002'
insert into lin2 select 'YCGK100817004'
insert into lin2 select 'YCGK100817005'
select 车辆信息=isnull(max(right(车辆信息,3)),000) from lin2 where substring(车辆信息,5,6)=substring(convert(varchar(8),getdate(),112),3,6)go
drop table lin2