字段a,字段b,新表字段c,字段a字段b是一个数字区间,如果依次插入到新的一张表字段C里面表1
————————————
字段a,字段b,字段d
1,10,0
15,25,1表2
————————————
字段c
1
2
3
4
5
6
7
8
9
10
怎么写这个语句,首先查询表1中字段d=0的符合条件区间,其次将区间拆解插入到表2字段c中,最后更新表1字段d=1。
————————————
字段a,字段b,字段d
1,10,0
15,25,1表2
————————————
字段c
1
2
3
4
5
6
7
8
9
10
怎么写这个语句,首先查询表1中字段d=0的符合条件区间,其次将区间拆解插入到表2字段c中,最后更新表1字段d=1。
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([字段a] int,[字段b] int,[字段d] int)
Insert #T
select 1,10,0 union all
select 15,25,1
GO
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([字段c] int)
--测试数据结束
INSERT INTO #T1
SELECT b.number
FROM #T
INNER JOIN master.dbo.spt_values AS b ON b.type = 'P'
WHERE b.number >= 字段a
AND b.number <= 字段bSELECT * FROM #T1结果:
谢谢,请问如果字段a 不是int类型 ,是varchar(15)呢,你的sql会出现溢出怎么解决呢?疑惑
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([字段a] varchar(15),[字段b] varchar(15),[字段d] int)
Insert #T
select '88000029286001','88000029286010',0 union all
select '88000029286021','88000029286020',1
GO
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([字段c] VARCHAR(15))
--测试数据结束
DECLARE @a1 BIGINT,@a2 BIGINT
SELECT @a1=字段a,@a2=字段b FROM #T WHERE 字段d=0
WHILE @a1<=@a2
BEGIN
INSERT INTO #T1
( 字段c )
VALUES ( LTRIM(@a1) -- 字段c - varchar(15)
)
SET @a1 = @a1+1
END
SELECT * FROM #T1
只要算差值,照样可以用#1的批量插入啊。
再改改
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([字段a] varchar(15),[字段b] varchar(15),[字段d] int)
Insert #T
select '88000029286001','88000029286010',0 union all
select '88000029286021','88000029286020',1
GO
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([字段c] VARCHAR(15))
--测试数据结束
INSERT INTO #T1
SELECT CAST(字段a AS BIGINT) + b.number
FROM #T
INNER JOIN master.dbo.spt_values AS b ON b.type = 'P'
WHERE CAST(字段a AS BIGINT) + b.number <= CAST(字段b AS BIGINT);
SELECT * FROM #T1