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表
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表
n06002ccc select max(substring(字段a,4,3)) from tablea where a like '?06%' and b = 'ccc'够简单吧!哈哈
select max(substring(字段a,4,3)) from tablea where a like '_06%' and b = 'ccc'
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))
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
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'
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表
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表
select max(substring(字段a,4,3)) from tablea where a like '?06%' and b = 'ccc'够简单吧!哈哈
select 'N06'+max(substring(字段a,4,3))+'ccc' from A表 where 字段a like '_06%' and 字段a like '%ccc'
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))
(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
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'