111,097
社区成员




--DROP TABLE #T
SELECT NUMBER INTO #T FROM MASTER..SPT_VALUES WHERE TYPE='P' AND NUMBER BETWEEN 1 AND 190
ALTER TABLE #T ADD ID INT
UPDATE #T SET ID=(NUMBER-1)/19+1
SELECT * FROM #T
/*NUMBER ID
----------- -----------
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 1
10 1
11 1
12 1
13 1
14 1
15 1
16 1
17 1
18 1
19 1
20 2
21 2
22 2
23 2
24 2
25 2
26 2
27 2
28 2
29 2
30 2
31 2
32 2
33 2
34 2
35 2
36 2
37 2
38 2
39 3
40 3
41 3
42 3
43 3
44 3
45 3
46 3
47 3
48 3
49 3
50 3
51 3
52 3
53 3
54 3
55 3
56 3
57 3
58 4
59 4
60 4
61 4
62 4
63 4
64 4
65 4
66 4
67 4
68 4
69 4
70 4
71 4
72 4
73 4
74 4
75 4
76 4
77 5
78 5
79 5
80 5
81 5
82 5
83 5
84 5
85 5
86 5
87 5
88 5
89 5
90 5
91 5
92 5
93 5
94 5
95 5
96 6
97 6
98 6
99 6
100 6
101 6
102 6
103 6
104 6
105 6
106 6
107 6
108 6
109 6
110 6
111 6
112 6
113 6
114 6
115 7
116 7
117 7
118 7
119 7
120 7
121 7
122 7
123 7
124 7
125 7
126 7
127 7
128 7
129 7
130 7
131 7
132 7
133 7
134 8
135 8
136 8
137 8
138 8
139 8
140 8
141 8
142 8
143 8
144 8
145 8
146 8
147 8
148 8
149 8
150 8
151 8
152 8
153 9
154 9
155 9
156 9
157 9
158 9
159 9
160 9
161 9
162 9
163 9
164 9
165 9
166 9
167 9
168 9
169 9
170 9
171 9
172 10
173 10
174 10
175 10
176 10
177 10
178 10
179 10
180 10
181 10
182 10
183 10
184 10
185 10
186 10
187 10
188 10
189 10
190 10
(所影响的行数为 190 行)
select identity(int,1,1) as code ,0 as id,* into #temp from 表名
order by XXX
update #temp set id = code
update #temp set id = ceiling(cast(id as float) / 19)
alter table #temp drop column code
select * from #temp
select identity(int,1,1) as code ,0 as id,* into #temp from 表名 order by user_name
update #temp set id = code
update #temp set id = ceiling(cast(id as float) / 19)
alter table #temp drop column code
select * from #temp
declare @rowCount int
declare @Sql varchar(1000)
select @rowCount=count(0) from 表名
while @rowCount>0
begin
set @Sql = 'update 表名 set id='+convert(varchar, (@rowCount+9)/10)+' where 主键 in(select top '+convert(varchar, @rowCount)+' 主键 from 表名)'
exec(@Sql)
set @rowCount = @rowCount - 10
end