请教一个批量生成数据的问题

[IT小卜]

两个参数，@ID是数据，@Count生成数据的行数
比例@ID=5000，@Count=4
批量生成
5000
4999
4998
4997
语句怎么写，有没有效率高的写法，我试过用Row_Number不行，因为没有主键。Identify（）可以吗？

[lzw_0736]

CREATE TABLE #temp(ID INT)

DECLARE @ID INT,@Count int
select @ID=5000,@Count=4

;WITH a1 AS
(
SELECT @ID id,1 n
UNION ALL
SELECT ID-1,n+1 FROM a1
WHERE n<@Count
)
INSERT #temp
SELECT id FROM a1

SELECT * FROM #temp

[LongRui888]

declare @id int
declare @count int


select @ID=5000,@Count=4

select v.*
from 
(
select @id id,@count c
)v,master..spt_values t
where t.type = 'P' and t.number > 0 and t.number <= v.c
/*
id	     c
5000	4
5000	4
5000	4
5000	4
*/

[Yole]

--测试数据
if object_id('t') is not null drop table t
go 
create table t([ID] int)
declare @n int,  @ID int,  @Count int
set @ID=5000
set @Count=4
set @n=0
--循环插入
while @n<@Count
begin
insert t
select @ID-@n
select @n=@n+1
end
select * from t
drop table t

(1 行受影响) (1 行受影响) (1 行受影响) (1 行受影响) ID ----------- 5000 4999 4998 4997

[LongRui888]

改了一下：

declare @id int
declare @count int
declare @tb table(id int,c int)

insert into @tb
select 5000,4 union all
select 4999,4 union all
select 4998,4 union all
select 4997,4 


select v.*
from @tb v,master..spt_values t
where t.type = 'P' and t.number > 0 and t.number <= v.c
/*
id	c
5000	4
5000	4
5000	4
5000	4
4999	4
4999	4
4999	4
4999	4
4998	4
4998	4
4998	4
4998	4
4997	4
4997	4
4997	4
4997	4
*/

[IT小卜]

1楼的写法基本能满足我的需求，不过也要修改下下，

DECLARE @ID INT ,
    @Count INT
SELECT  @ID = 5000 ,
        @Count = 100;
WITH    a1
          AS ( SELECT   @ID id ,
                        1 n
               UNION ALL
               SELECT   @ID - n  ,
                        n + 1
               FROM     a1
               WHERE    n < @Count
             )
    
            SELECT  id
            FROM    a1

[IT小卜]

1楼的写法基本能满足我的需求，不过也要修改下下，

DECLARE @ID INT ,
    @Count INT
SELECT  @ID = 5000 ,
        @Count = 100;
WITH    a1
          AS ( SELECT   @ID id ,
                        1 n
               UNION ALL
               SELECT   @ID - n  ,
                        n + 1
               FROM     a1
               WHERE    n < @Count
             )
    
            SELECT  id
            FROM    a1