34,838
社区成员




create table #t(bianhao varchar(20))
insert into #t
select 'G0905181'
UNION ALL
select 'J090518101'
UNION ALL
select 'G09051821'
UNION ALL
select 'G0905182'
UNION ALL
select 'B09051832'
UNION ALL
select 'G09051822'
UNION ALL
select 'J09051811'
UNION ALL
select 'Q09051813'
union all
select 'B090518121'
bianhao的格式为:分类代码+日期+序列号,例如:'B'+'090518'+'121'
我想查询出来,今天090518下的序列号最大的一条数据(’B090518121'),SQL语句如何写?
create table #t(bianhao varchar(20))
insert into #t
select 'G0905181'
UNION ALL
select 'J090518101'
UNION ALL
select 'G09051821'
UNION ALL
select 'G0905182'
UNION ALL
select 'B09051832'
UNION ALL
select 'G09051822'
UNION ALL
select 'J09051811'
UNION ALL
select 'Q09051813'
union all
select 'B090518121'
--SELECT * FROM #T
DECLARE @VAR INT
SELECT @VAR=MAX(CONVERT(INT,RIGHT(BIANHAO,LEN(BIANHAO)-7))) FROM #T
SELECT * FROM #T WHERE CHARINDEX(CONVERT(VARCHAR,@VAR),BIANHAO,7 ) >0
/*bianhao
--------------------
B090518121
(所影响的行数为 1 行)
*/
create table #t(bianhao varchar(20))
insert into #t
select 'G0905181'
UNION ALL
select 'J090518101'
UNION ALL
select 'G09051821'
UNION ALL
select 'G0905182'
UNION ALL
select 'B09051832'
UNION ALL
select 'G09051822'
UNION ALL
select 'J09051811'
UNION ALL
select 'Q09051813'
union all
select 'B090518121'
-- bianhao的格式为:分类代码+日期+序列号,例如:'B'+'090518'+'121'
-- 我想查询出来,今天090518下的序列号最大的一条数据(’B090518121'),SQL语句如何写?
select top 1 bianhao from #t where charindex(right(convert(varchar(10),getdate(),112),6),bianhao)>0 order by cast(substring(bianhao,8,len(bianhao)-8) as int) desc
drop table #t
/*
bianhao
--------------------
B090518121
*/