取最大一条数据，sql如何写？

daring1981 2009-05-18 07:33:31



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语句如何写？

...全文

94 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

SQL77 2009-05-18

打赏
举报

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 行）



*/

sdhdy 2009-05-18

打赏
举报

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

*/