表A有ID字段,int类型,里面的值的范围是0-100,可以重复且乱序
我想起请教下各位如何写一个语句查找出,不等于表内的任何一个ID,且范围是0-100的最小数字.
小弟写的语句思路是这样
select distinct id from a
查找出所有ID,然后select top 1 id .....where id not in (上述ID) and (id between 0 and 100) order by id asc
思路大概是这样,但是不知道如何用语句把他们组合在一起,请各位大大指点,谢谢了.
------解决方案--------------------
- SQL code
-->生成测试数据:GOIF OBJECT_ID('TBL')IS NOT NULLDROP TABLE TBLGOCREATE TABLE TBL(ID INT)GOINSERT TBLSELECT 1 UNION ALLSELECT 3 UNION ALLSELECT 5 UNION ALLSELECT 6 UNION ALLSELECT 1 UNION ALLSELECT 12--利用递归实现输出三月份的所有日期：select coalesce(min(a.id)+1,1) as idfrom tbl a where not exists (select 1 from tbl bwhere b.id=a.id+1) and exists (select 1 from tbl where id=1)/*id2*/