-->生成测试数据: GO IF OBJECT_ID('TBL')IS NOT NULL DROP TABLE TBL GO CREATE TABLE TBL( ID INT ) GO INSERT TBL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 1 UNION ALL SELECT 12 --利用递归实现输出三月份的所有日期: go declare @ID INT select @ID=MAX(ID) from tbl ;with t as( select * from tbl union all select a.ID+1 from t a where not exists(select * from tbl b where b.ID=a.ID+1 ) and a.ID<@ID ) select distinct *from t where ID not in( select ID from tbl ) order by ID/* ID 2 4 7 8 9 10 11 */
你那个问题就是“最小缺失数”问题: select coalesce(min(a.key)+1,1) as id from tbl a where not exists (select select 1 from tbl b where b.key=a.key+1) and exists ( select 1 from tbl where id=1 )key 改成id即可
-->生成测试数据: GO IF OBJECT_ID('TBL')IS NOT NULL DROP TABLE TBL GO CREATE TABLE TBL( ID INT ) GO INSERT TBL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 1 UNION ALL SELECT 12 --利用递归实现输出三月份的所有日期: select coalesce(min(a.id)+1,1) as id from tbl a where not exists (select 1 from tbl b where b.id=a.id+1) and exists ( select 1 from tbl where id=1 ) /* id 2 */
-->生成测试数据:
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
ID INT
)
GO
INSERT TBL
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 1 UNION ALL
SELECT 12
--利用递归实现输出三月份的所有日期:
go
declare @ID INT
select @ID=MAX(ID) from tbl
;with t
as(
select * from tbl
union all
select a.ID+1 from t a
where not exists(select * from tbl b
where b.ID=a.ID+1
)
and a.ID<@ID
)
select distinct *from t where ID not in(
select ID from tbl
) order by ID/*
ID
2
4
7
8
9
10
11
*/
你那个问题就是“最小缺失数”问题:
select coalesce(min(a.key)+1,1) as id
from tbl a where not exists (select select 1 from tbl b
where b.key=a.key+1) and exists (
select 1 from tbl where id=1
)key 改成id即可
-->生成测试数据:
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
ID INT
)
GO
INSERT TBL
SELECT 1 UNION ALL
SELECT 3 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 1 UNION ALL
SELECT 12
--利用递归实现输出三月份的所有日期:
select coalesce(min(a.id)+1,1) as id
from tbl a where not exists (select 1 from tbl b
where b.id=a.id+1) and exists (
select 1 from tbl where id=1
)
/*
id
2
*/