如何查询表中不存在的最小数
列 name 的类型是 char(1) , 值是字符A到字符Z ,26个字母中的一个

所有记录的 name 值 是不连续的


如何查询 26个字母中那些不在记录中的字母中的最小的一个?

[解决办法]

drop table tb
Create table tb(ch char(1));
Insert into tb(ch) values('A'),('C');

Select *,identity(int,1,1)as rn
Into ot
From tb
order by ASCII(ch);

Select top(1) CHAR(ASCII(t1.ch) +1) as minchar
From ot as t1
Left Join  ot as t2
On ASCII(t1.ch) = ASCII(t2.ch) -1
Where t2.rn is null
-----
B

[解决办法]

引用:

number 是什么啊?

--> number是系统表spt_values的字段,在此用于产生26个字母.

create table ayu(name char(1))

insert into ayu
  select 'A' union all
  select 'C' union all
  select 'E'


select min(a.name) 'name'
 from
 (select char(64+number) 'name'
  from master.dbo.spt_values
  where [type]='P' and number between 1 and 26) a
 left join
 (select distinct [name] from ayu) b on a.name=b.name
 where b.name is null

/*
name
----
B

(1 row(s) affected)
*/