查询最大值问题，谢谢先

[yuanqi007]

A表中的字段a的数据格式为：Nyyxxxccc

其中N为字符N或T
yy为年份
xxx为001 - 999之间的数值
ccc可能为数值，也可以为字符

现想查询N相同的，同一年份的，ccc相同的，xxx的最大值

谢谢各位先

[yuanqi007]

不好意思，没介绍太清楚

比如
a b
n06001ccc ccc
n06002ccc ccc
n05003ccc ccc
n05001aaa aaa

现想查询06年CCC的最大的a
查询结果
n06002ccc

[子陌红尘]

select
t.*
from
A表 t
where
not exists(select
1
from
A表
where
left(字段a,3) = left(t.字段a,3)
and
right(字段a,3) = right(t.字段a,3)
and
substring(字段a,4,3) > substring(t.字段a,4,3))

[子陌红尘]

--生成测试数据
create table A表(字段a varchar(10))
insert into A表 select 'N06001ccc'
insert into A表 select 'N06002ccc'
insert into A表 select 'N05010ccc'
insert into A表 select 'N05001ccc'
insert into A表 select 'N05011ccc'
insert into A表 select 'N04101ccc'
insert into A表 select 'N04001ccc'
insert into A表 select 'N04111ccc'
insert into A表 select 'N03001ccc'
insert into A表 select 'N03003ccc'


--执行查询处理
select
t.*
from
A表 t
where
not exists(select
1
from
A表
where
left(字段a,3) = left(t.字段a,3)
and
right(字段a,3) = right(t.字段a,3)
and
substring(字段a,4,3) > substring(t.字段a,4,3))

--输出结果
/*
字段a
----------
N06002ccc
N05011ccc
N04111ccc
N03003ccc
*/

--删除测试数据
drop table A表

[zlp321002]

select * from 表 A
where not exists
(select 1 from 表 where
substring(字段,1,1)=substring(A.字段,1,1)
and substring(字段,2,2)=substring(A.字段,2,2)
and substring(字段,7,3)=substring(A.字段,7,3)
and substring(字段,4,3)>substring(A.字段,4,3)
)

[子陌红尘]

--生成测试数据
create table A表(字段a varchar(10))
insert into A表 select 'N06001ccc'
insert into A表 select 'N06002ccc'
insert into A表 select 'N05010ccc'
insert into A表 select 'N05001ccc'
insert into A表 select 'N05011ccc'
insert into A表 select 'N04101ccc'
insert into A表 select 'N04001ccc'
insert into A表 select 'N04111ccc'
insert into A表 select 'N03001ccc'
insert into A表 select 'N03003ccc'


--执行查询处理
select
t.*
from
A表 t
where
substring(t.字段a,2,2) = '06'
and
not exists(select
1
from
A表
where
left(字段a,3) = left(t.字段a,3)
and
right(字段a,3) = right(t.字段a,3)
and
substring(字段a,4,3) > substring(t.字段a,4,3))

--输出结果
/*
字段a
----------
N06002ccc
*/

--删除测试数据
drop table A表

[fesxe]

n06002ccc
select max(substring(字段a,4,3)) from tablea where a like '?06%' and b = 'ccc'

够简单吧!哈哈

[fesxe]

select max(substring(字段a,4,3)) from tablea where a like '_06%' and b = 'ccc'

[fesxe]

select 'N06'+max(substring(字段a,4,3))+'ccc' from A表 where 字段a like '_06%' and 字段a like '%ccc'

[云中客]

select t.* from A表 t
where not exists(select 1 from A表
where left(字段a,3)= left(t.字段a,3)
and right(字段a,3)= right(t.字段a,3)
and substring(字段a,4,3) > substring(t.字段a,4,3))

[zkc727]

select left(s.t1,3)+s.t2+right(s.t1,3) from
(select left(a,3)+right(a,3) as t1,max(substring(a,4,3)) as t2
from A表
group by left(a,3)+right(a,3)) s

[LiloZhu]

select t.* from table_a t
where not exists(select 1 from table_a
where substring(file_a,1,1)=substring(t.file_a,1,1)
and substring(file_a,2,2)=substring(t.file_a,2,2)
and substring(file_a,7,3)=substring(t.file_a,7,3)
and substring(file_a,4,3)>substring(t.file_a,4,3))
and substring(t.file_a,2,2)='06'

[lsqkeke]

呵呵 学习 ：）