求SQL语句：存在就跳过，不存在就插入记录，从字段ID=1开始，到字段ID=1某个值结束？

如题：我现在有一张表，其中里面有字段为HangNo,字段类型为int,我想通过一条SQL语句来实现：循环插入记录操作，让记录的HangNo=1插入到HangNo=n,其中n大于或等于1，如果在表中已经存在HangNo等于要插入的值，则跳过该记录
表中可能存在这些情况：
1，表中一条记录都没有，HangNo字段从1到n,插入n条记录；
2. 表中有一些记录，如果存在HangNo字段值等于要插入的值，则跳过该记录，不存在则插入该记录。
3. 表中存在HangNo字段从1到n的记录，甚至HangNo字段的记录个数超出n条，实际并不会插入任何记录。
这个SQL循环插入不存在的记录的语句该怎么写？最好能MySQL和SQL SERVER中都支持该语法

9 个解决方案

#1

--如何用"最小缺失数"实现确实日期的自动补全
-->生成测试数据:
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
日期 DATE,
备注 VARCHAR(100)
)
GO
INSERT TBL
SELECT '2012-03-02','B' UNION ALL
SELECT '2012-03-05','C' UNION ALL
SELECT '2012-03-06','D' UNION ALL
SELECT '2012-03-07','E' UNION ALL
SELECT '2012-03-09','F' UNION ALL
SELECT '2012-03-11','G' UNION ALL
SELECT '2012-03-12','H' UNION ALL
SELECT '2012-03-13','I' UNION ALL
SELECT '2012-03-15','J' UNION ALL
SELECT '2012-03-19','K' UNION ALL
SELECT '2012-03-20','L'


GO
IF OBJECT_ID('P_SP')IS NOT NULL
DROP PROC P_SP
GO
CREATE PROC P_SP 
@STARTTIME DATE,--用来传入起始日期
@ENDTIME DATE--用来传入截止日期
AS
DECLARE @SQL VARCHAR(100)
SET @SQL='SELECT * FROM TBL ORDER BY 日期'
DECLARE @MINMISS DATE
SET @MINMISS=(
SELECT COALESCE(DATEADD(DD,1,MIN(A.日期)),
@STARTTIME) AS MISSING
FROM TBL A
WHERE NOT EXISTS(
SELECT * FROM TBL B 
WHERE B.日期=DATEADD(DD,1,A.日期))
AND EXISTS (
SELECT 1 FROM TBL WHERE 日期=@STARTTIME))
PRINT @MINMISS
WHILE @MINMISS<=@ENDTIME
BEGIN
INSERT TBL(日期) VALUES(@MINMISS)
SELECT @MINMISS=(
SELECT DATEADD(DD,1,MIN(A.日期))
FROM TBL A
WHERE NOT EXISTS(
SELECT * FROM TBL B 
WHERE B.日期=DATEADD(DD,1,A.日期))
)
END
EXEC(@SQL)

EXEC P_SP '2012-03-01','2012-03-20'

/*
日期	备注
2012-03-01	NULL
2012-03-02	B
2012-03-03	NULL
2012-03-04	NULL
2012-03-05	C
2012-03-06	D
2012-03-07	E
2012-03-08	NULL
2012-03-09	F
2012-03-10	NULL
2012-03-11	G
2012-03-12	H
2012-03-13	I
2012-03-14	NULL
2012-03-15	J
2012-03-16	NULL
2012-03-17	NULL
2012-03-18	NULL
2012-03-19	K
2012-03-20	L
*/

类似问题，刚刚写好的，给你参考一下

#2

--如何用"最小缺失数"实现确实日期的自动补全
-->生成测试数据:
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
行号 INT,
备注 VARCHAR(100)
)
GO
INSERT TBL
SELECT 1,'B' UNION ALL
SELECT 2,'C' UNION ALL
SELECT 4,'D' UNION ALL
SELECT 6,'E' UNION ALL
SELECT 10,'F' UNION ALL
SELECT 11,'G' UNION ALL
SELECT 13,'H' UNION ALL
SELECT 14,'I' UNION ALL
SELECT 16,'J' UNION ALL
SELECT 18,'K' UNION ALL
SELECT 20,'L'


GO
IF OBJECT_ID('P_SP')IS NOT NULL
DROP PROC P_SP
GO
CREATE PROC P_SP 
@START INT,--用来传入起始行号
@END INT--用来传入截止行号
AS
DECLARE @SQL VARCHAR(100)
SET @SQL='SELECT * FROM TBL ORDER BY 行号'
DECLARE @MINMISS INT
SET @MINMISS=(
SELECT COALESCE(MIN(A.行号)+1,
@START) AS MISSING
FROM TBL A
WHERE NOT EXISTS(
SELECT * FROM TBL B 
WHERE B.行号=A.行号+1)
AND EXISTS (
SELECT 1 FROM TBL WHERE 行号=@START))
PRINT @MINMISS
WHILE @MINMISS<=@END
BEGIN
INSERT TBL(行号) VALUES(@MINMISS)
SELECT @MINMISS=(
SELECT MIN(A.行号)+1
FROM TBL A
WHERE NOT EXISTS(
SELECT * FROM TBL B 
WHERE B.行号=A.行号+1
))
END
EXEC(@SQL)

EXEC P_SP 1,22

/*
行号	备注
1	B
2	C
3	NULL
4	D
5	NULL
6	E
7	NULL
8	NULL
9	NULL
10	F
11	G
12	NULL
13	H
14	I
15	NULL
16	J
17	NULL
18	K
19	NULL
20	L
21	NULL
22	NULL
*/

#3

--如何用"最小缺失数"实现确实日期的自动补全
-->生成测试数据:
GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
行号 INT,
备注 VARCHAR(100)
)
GO
INSERT TBL
SELECT 1,'B' UNION ALL
SELECT 2,'C' UNION ALL
SELECT 4,'D' UNION ALL
SELECT 6,'E' UNION ALL
SELECT 10,'F' UNION ALL
SELECT 11,'G' UNION ALL
SELECT 13,'H' UNION ALL
SELECT 14,'I' UNION ALL
SELECT 16,'J' UNION ALL
SELECT 18,'L' UNION ALL
SELECT 22,'M' UNION ALL
SELECT 29,'N' UNION ALL
SELECT 31,'O'


GO
IF OBJECT_ID('P_SP')IS NOT NULL
DROP PROC P_SP
GO
CREATE PROC P_SP 
@START INT,--用来传入起始行号
@END INT--用来传入截止行号
AS
DECLARE @SQL VARCHAR(100)
SET @SQL='SELECT * FROM TBL ORDER BY 行号'
DECLARE @MINMISS INT
SET @MINMISS=(
SELECT COALESCE(MIN(A.行号)+1,
@START) AS MISSING
FROM TBL A
WHERE NOT EXISTS(
SELECT * FROM TBL B 
WHERE B.行号=A.行号+1)
AND EXISTS (
SELECT 1 FROM TBL WHERE 行号=@START))
PRINT @MINMISS
WHILE @MINMISS<=@END
BEGIN
INSERT TBL(行号) VALUES(@MINMISS)
SELECT @MINMISS=(
SELECT MIN(A.行号)+1
FROM TBL A
WHERE NOT EXISTS(
SELECT * FROM TBL B 
WHERE B.行号=A.行号+1
))
END
EXEC(@SQL)

EXEC P_SP 1,25

/*
行号	备注
1	B
2	C
3	NULL
4	D
5	NULL
6	E
7	NULL
8	NULL
9	NULL
10	F
11	G
12	NULL
13	H
14	I
15	NULL
16	J
17	NULL
18	L
19	NULL
20	NULL
21	NULL
22	M
23	NULL
24	NULL
25	NULL
29	N
31	O
*/

--注意看结果出来的数据，正好满足你的第三个要求，也就是到n就停止了，后面的数据并没有补全

#4

没有能一条稍长的语句搞定的吗？问一下，这么长我怎么嵌到MFC源码中，要在数据库中写存储过程吗

#5

引用 4 楼 kgduwu 的回复:

没有能一条稍长的语句搞定的吗？问一下，这么长我怎么嵌到MFC源码中，要在数据库中写存储过程吗

可以用系统表来构造
但是存储过程可以直接在程序中调用，不用你写代码到程序里
只需你在数据库的企业管理器里面创建好就是了


我也是刚学了这个“最小缺失数”问题才这么写的

给你一个用系统表构造的例子吧：

/*
    create table #tB(
    [A] int,
    [C2] varchar(10),
    [C3] datetime
    ) 
    insert #tB 
    select 1,'dfgsdfgsdf','2010-02-01' union all 
    select 2,'dfgsdfgsdf','2010-02-02' union all 
    select 3,'dfgsdfgsdf','2010-02-03' union all 
    select 4,'dfgsdfgsdf','2010-02-04' union all 
    select 4,'dfgsdfgsdf','2010-09-04' union all 
    select 5,'dfgsdfgsdf','2010-09-08' union all 
    select 5,'dfgsdfgsdf','2010-03-08' union all 
    select 6,'dfgsdfgsdf','2010-03-11' union all 
    select 4,'dfgsdfgsdf','2010-05-04' union all 
    select 5,'dfgsdfgsdf','2010-02-08' union all 
    select 6,'dfgsdfgsdf','2010-05-11' union all 
    select 7,'dfgsdfgsdf','2010-05-14' union all 
    select 8,'dfgsdfgsdf','2010-05-16' union all 
    select 7,'dfgsdfgsdf','2010-03-14' union all 
    select 8,'dfgsdfgsdf','2010-03-16' union all 
    select 6,'dfgsdfgsdf','2010-09-11' union all 
    select 7,'dfgsdfgsdf','2010-09-14' union all 
    select 8,'dfgsdfgsdf','2010-09-16' union all 
    select 9,'dfgsdfgsdf','2010-11-17'


想得到如下结果

SQL code

    month total percent 
    2010-01 0 .... 
    2010-02 14 .... 
    2010-03 26 .... 
    2010-04 0 ....
    2010-05 25 .... 
    2010-06 0 .... 
    2010-07 0 .... 
    2010-08 0 .... 
    2010-09 25 .... 
    2010-10 0 .... 
    2010-11 9 .... 
    2010-12 0 ....


*/
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
[A] int,
[C2] varchar(10),
[C3] datetime
) 
insert tbl 
select 1,'dfgsdfgsdf','2010-02-01' union all 
select 2,'dfgsdfgsdf','2010-02-02' union all 
select 3,'dfgsdfgsdf','2010-02-03' union all 
select 4,'dfgsdfgsdf','2010-02-04' union all 
select 4,'dfgsdfgsdf','2010-09-04' union all 
select 5,'dfgsdfgsdf','2010-09-08' union all 
select 5,'dfgsdfgsdf','2010-03-08' union all 
select 6,'dfgsdfgsdf','2010-03-11' union all 
select 4,'dfgsdfgsdf','2010-05-04' union all 
select 5,'dfgsdfgsdf','2010-02-08' union all 
select 6,'dfgsdfgsdf','2010-05-11' union all 
select 7,'dfgsdfgsdf','2010-05-14' union all 
select 8,'dfgsdfgsdf','2010-05-16' union all 
select 7,'dfgsdfgsdf','2010-03-14' union all 
select 8,'dfgsdfgsdf','2010-03-16' union all 
select 6,'dfgsdfgsdf','2010-09-11' union all 
select 7,'dfgsdfgsdf','2010-09-14' union all 
select 8,'dfgsdfgsdf','2010-09-16' union all 
select 9,'dfgsdfgsdf','2010-11-17'


select 
isnull(c1,'2010-'+right('00'+ltrim(number),2)) as [month],--实现按月份递增
isnull(c2,0) as total,
ltrim(cast(isnull(c2,0)*100*1.0/(select sum([A]) from tbl) as decimal(18,2)))+'%' as [percent]
--求百分比
 from master..spt_values  b 
left join 
(select convert(varchar(7),C3,120) as c1,sum([A]) as c2 from tbl 
group by convert(varchar(7),C3,120)
) c on b.number=month(c.c1+'-01') where b.type='p' and b.number between 1 and 12

/*
month	total	percent
2010-01	0	0.00%
2010-02	15	14.29%
2010-03	26	24.76%
2010-04	0	0.00%
2010-05	25	23.81%
2010-06	0	0.00%
2010-07	0	0.00%
2010-08	0	0.00%
2010-09	30	28.57%
2010-10	0	0.00%
2010-11	9	8.57%
2010-12	0	0.00%
*/

#6

其它字段插入时怎么处理，给出输入格式

#7

其它字段插入时，会根据HangNo的值（从1开始每条记录HangNo的值依次递增），拼接成默认的值，这个只要插入记录时HangNo的值确定了其它值也就确定是一个默认值了，至于已经存在的记录，因为已经跳过不管，因此如果用户更新替换那些默认值，也不会被丢失

#8

假如你的1到N在某个表tb1,要插入表tb2

insert into tb2 select ... from tb1 where not exists(select 1 from tb2 where tb2.关键字 = tb1.关键字) 
 
如果不存在表tb1,则可以使用系统表sysobjects来生成你的序列表.
select (select count(1) from sysobjects m where m.id < n.id) + 1 as px from sysobjects n

#9

http://blog.csdn.net/travylee/article/details/7336245

实现这类问题的三种方法