插入数据的问题

Question

 500行数据【可以用循环插入】，但是【data值】是每隔四行数据一个相同值，如下例子
顺序号     数据值         data值
1           1             data1
2           2             data1
3           3             data1
4           4             data1
5           5             data2
6           6             data2
7           7             data2
8           8             data2
9           9             data3
10          10            data3
11          11            data3
12          12            data3
13          13            data4
..................................
497         497            data125
498         498            data125
499         499            data125
500         500            data125
怎么实现呢，如果不能实现，先插入后更新的方式也可以啊，只要能得到结果就行。谢谢，在线等。就这么多分了，全部奉上，不好意思。

Answer 1

要是dota游戏就好了，嚎嚎

Answer 2

select
 'data'+ltrim((a.number-1)/4+1) as data值,b.number 
from
 (select id=row_number()over(order by getdate()),* from master..spt_values where type='p' and number between 1 and 500)a ,master..spt_values b
where
  b.type='p' and b.number between 1 and 500
and
  a.id=b.number
  /*data值            number
---------------- -----------
data1            1
data1            2
data1            3
data1            4
data2            5
data2            6
data2            7
data2            8
data3            9
data3            10
data3            11
data3            12
data4            13
data4            14
data4            15
data4            16
data5            17
data5            18
data5            19
data5            20
data6            21
data6            22
data6            23
data6            24
data7            25
data7            26
data7            27
data7            28
data8            29
data8            30
data8            31
data8            32
data9            33
data9            34
data9            35
data9            36
data10           37
data10           38
data10           39
data10           40
data11           41
data11           42
data11           43
data11           44
data12           45
data12           46
data12           47
data12           48
data13           49
data13           50
data13           51
data13           52
data14           53
data14           54
data14           55
data14           56
data15           57
data15           58
data15           59
data15           60
...(500 行受影响)*/

Answer 3

if object_id('[tb]') is not null drop table [tb]
create table [tb] (顺序号 int,数据值 int,data值 varchar(20))declare @i int
set @i=1
while @i<=500
begin
insert into tb(顺序号,数据值,data值)
select @i,@i,'data'+ltrim((@i+3)/4)
set @i=@i+1
endselect * from tbdrop table tb/*
顺序号         数据值         data值
----------- ----------- --------------------
1           1           data1
2           2           data1
3           3           data1
4           4           data1
5           5           data2
6           6           data2
7           7           data2
8           8           data2
...         ...         ...
497         497         data125
498         498         data125
499         499         data125
500         500         data125(500 行受影响)

Answer 4

看起来很复杂。from master..spt_values......?

Answer 5

谢谢，想在winform程序里用到。

Answer 6

create table #T
(
  id int identity(1,1) primary key,
  num int not null,
  data varchar(10)
)
declare @n int
set @n = 1
while @n <= 500
begin
   declare @y int
   if @n % 4 = 0
     set @y = @n / 4
   else
     set @y = @n / 4 + 1
   insert into #T(num,data) values(@n,'data'+convert(varchar(5),@y))
   set @n = @n + 1
end  
select * from #T order by id

Answer 7

根据大家的解决方法，程序里面写的话 是这样的吗for (i=1,i<500,i++)
 {
    insert into tb values(i,i,'data'+ltrim((i+3)/4)
}