求sql语句,很有挑战性！！

Question

序号从80001 开始 
SQL codecNum ( varchar(30))                          
------------------------------ 
JZ80002
JZ80004
XM80003
XM80005
JZ80005预期结果：
JZ80001
JZ80003
XM80001
XM80002
XM80004最后的结果（上面最小的）： JZ80001 
xm80001

Answer 1

最后的结果（上面最小的）： JZ80001 
xm80001－－
select distinct left(Num,2) + '80001'
from at

Answer 2

create table #tb(fnumber varchar(40))
insert into #tb
select 'JZ80002'
union all
select 'JZ80004'
union all
select 'XM80003'
union all
select 'XM80005'
union all
select 'JZ80005'select top 1000 fnum=identity(int,1,1) into #Date1 from syscolumns 
select left(fnumber,len(fnumber)-4) fnumber,max(right(fnumber,4)) fnum 
into #Date2 from #tb
group by left(fnumber,len(fnumber)-4) declare @maxnum int
select @maxnum=cast(max(right(fnumber,4)) as int) from #tbselect t1.fnumber,t2.fnum 
into #Date3 from #Date2 t1 cross join 
(select fnum from #Date1 where fnum<=@maxnum) t2select t1.fnumber,t1.fnum
into #Date4 from #Date3 t1 
inner join #Date2 t2 on t1.fnumber=t2.fnumber and t1.fnum<=t2.fnumselect fnumber+right(convert(varchar(20),10000+fnum),4) fnumber from #Date4 t1
where fnumber+right(convert(varchar(20),10000+fnum),4) not in 
(select fnumber from #tb)select fnumber+right(convert(varchar(20),10000+min(fnum)),4) fnumber from #Date4 t1
where fnumber+right(convert(varchar(20),10000+fnum),4) not in 
(select fnumber from #tb)
group by fnumberdrop table #tb
drop table #Date1
drop table #Date2
drop table #Date3
drop table #Date4

Answer 3

sql 2000 平台，最优化的代码，实现其功能，避免使用 not in ，因为数据 有10W条呢

Answer 4

select codecnum from @ac a 
where not exists(select 1 from @ac b 
where left(a.codecnum,6) = left(b.codecnum,6)
 and right(a.codecnum,2)>right(b.codecnum,2) ) 

Answer 5

不知楼主所言“前缀是固定的”是以下哪种意思:
1、长度固定为2位
2、内容固定为"XM"或"ZJ"

Answer 6

既然楼主尚未回答，那就按难一点的第1种情况来做吧。
用游标来实现的，功能绝对没错。性能嘛，我一向以为，看似最笨的法子，也许就是最有效率的法子。呵呵。
脚本如下：-- 建库、建表、插入测试数据
create database TestDB
go
use TestDB
go
create table MyTable
(
codeNum varchar(30)
)
go
insert into MyTable(codeNum) values('JZ80002')
insert into MyTable(codeNum) values('JZ80004')
insert into MyTable(codeNum) values('XM80003')
insert into MyTable(codeNum) values('XM80005')
insert into MyTable(codeNum) values('JZ80005')
insert into MyTable(codeNum) values('AB80001')
insert into MyTable(codeNum) values('AB80003')
go
select codeNum from MyTable
go
-- 测试语句：查询表中所有前缀
select distinct left( codeNum, 2 ) from MyTable
go
-- 从此处开始，是楼主感兴趣的代码 :)
-- 创建存储过程
create procedure up_processCodeNum
as
-- 定义必要的局部变量
declare @num int
declare @prefix char(2)
declare @codeNum varchar(30)
-- 定义游标（查询表中所有前缀）
declare cur_codeNum cursor for select distinct left( codeNum, 2 ) from MyTable
-- 打开游标
open cur_codeNum
-- 通过游标循环对每个前缀进行操作
fetch next from cur_codeNum into @prefix
while @@FETCH_STATUS = 0
begin
-- 从80001开始递增，找到第一个不存在的号，即是该前缀中最小的断号
set @num = 80001
while 1=1
begin
set @codeNum = @prefix + convert(varchar,@num);
if not exists ( select 1 from MyTable where codeNum = @codeNum )
break;
set @num = @num + 1
end
-- 此处是直接输出结果而已，如有必要可以把结果保存到表中
print @codeNum fetch next from cur_codeNum into @prefix
end
-- 关闭并销毁游标
close cur_codeNum
deallocate cur_codeNum
go
-- 执行存储过程
execute up_processCodeNum

Answer 7

create table tb
(col1 varchar(8)
)
go
insert into tb
select 'xm80001'
union all select 'xm80002'
union all select 'xm80004'
union all select 'xm80007'
union all select 'jx80002'
union all select 'jx80003'
union all select 'jx80005'
union all select 'jx80009'
union all select 'jx80011'
union all select 'jx80020'
-----为了使用最小量的临时表
declare @row int
select @row=(select MAX(substring(col1,3,5)) from tb)-80000
set rowcount @row
select id=IDENTITY(int,1,1) into #t from syscolumns,sysobjects
set rowcount 0
select A.prefix,B.id from (
select prefix=SUBSTRING(col1,1,2),MAX(substring(col1,3,5)) maxnum from tb group by SUBSTRING(col1,1,2))a right join 
#t b on B.id+80000<=a.maxnum 
left join tb c on a.prefix+CAST(80000+b.id as varchar(5))=c.col1 
where c.col1 is null 
order by a.prefix,id
----最小值
select A.prefix,MIN(b.id) from( 
select prefix=SUBSTRING(col1,1,2),MAX(substring(col1,3,5)) maxnum from tb group by SUBSTRING(col1,1,2))a right join 
#t b on B.id+80000<=a.maxnum 
left join tb c on a.prefix+CAST(80000+b.id as varchar(5))=c.col1 
where c.col1 is null 
group by a.prefix
--------
--result1
jx 1
jx 4
jx 6
jx 7
jx 8
jx 10
jx 12
jx 13
jx 14
jx 15
jx 16
jx 17
jx 18
jx 19
xm 3
xm 5
xm 6
--result2
jx 1
xm 3

Answer 8

prefix+cast(80000+id as varchar(5)) 得到的则是结果字符串了