是ID字段
“2011010202020184”
“2011010702020185”
“2011011002020186”
“2011010802020287”
“2011010602020188”
“2011010402020189”
“2011010302020190”
“2011010502020191”
“2011010602030114”
“2011010602030115”
“2011010902030117”
“2011010602030118”
“2011010602030119”
“2011010102030119”
怎么查询ID第7-8位数的最大值。如上可以查询最大值是“2011011002020186”。
“2011010202020184”
“2011010702020185”
“2011011002020186”
“2011010802020287”
“2011010602020188”
“2011010402020189”
“2011010302020190”
“2011010502020191”
“2011010602030114”
“2011010602030115”
“2011010902030117”
“2011010602030118”
“2011010602030119”
“2011010102030119”
怎么查询ID第7-8位数的最大值。如上可以查询最大值是“2011011002020186”。
select top 1 id
from tb
order by substring(id,7,2) desc
create table tb(id varchar(20))
insert into tb
select '2011010202020184' union all
select '2011010702020185' union all
select '2011011002020186' union all
select '2011010802020287' union all
select '2011010602020188' union all
select '2011010402020189' union all
select '2011010302020190' union all
select '2011010502020191' union all
select '2011010602030114' union all
select '2011010602030115' union all
select '2011010902030117' union all
select '2011010602030118' union all
select '2011010602030119' union all
select '2011010102030119'
goselect top 1 id
from tb
order by substring(id,7,2) descdrop table tb/*********id
--------------------
2011011002020186(1 行受影响)
如果有多个呢?楼主。
from tb t
where not exists (select 1 from tb where substring(id,7,2) > substring(t.id,7,2))
create table tb(id varchar(20))
insert into tb
select '2011010202020184' union all
select '2011010702020185' union all
select '2011011002020186' union all
select '2011011002020213' union all --新加的和186字符串7-8位相同的数据行
select '2011010802020287' union all
select '2011010602020188' union all
select '2011010402020189' union all
select '2011010302020190' union all
select '2011010502020191' union all
select '2011010602030114' union all
select '2011010602030115' union all
select '2011010902030117' union all
select '2011010602030118' union all
select '2011010602030119' union all
select '2011010102030119'
goselect id
from tb t
where not exists (select 1 from tb where substring(id,7,2) > substring(t.id,7,2))drop table tb/************id
--------------------
2011011002020186
2011011002020213(2 行受影响)
insert into tb
select '2011010202020184' union all
select '2011010702020185' union all
select '2011011002020186' union all
select '2011011002020213' union all
select '2011010802020287' union all
select '2011010602020188' union all
select '2011010402020189' union all
select '2011010302020190' union all
select '2011010502020191' union all
select '2011010602030114' union all
select '2011010602030115' union all
select '2011010902030117' union all
select '2011010602030118' union all
select '2011010602030119' union all
select '2011010102030119'
goselect * from tb
where SUBSTRING(ID,7,2)=(select top 1 SUBSTRING(ID,7,2)
from tb order by SUBSTRING(ID,7,2) desc)
/*(15 行受影响)
id
--------------------
2011011002020186
2011011002020213(2 行受影响)