如何查询表中不存在的最小数
列 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
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)
*/
