求一条序列的语句其中包括获取系统的时间 ，使用convert(int,substring(convert(char,getdate(),112),1,6)+'0001')-10000 结果为2009110001 ，然后由2009110001为基础增加序列 ，要求不使用函数与存储过程，在一条语句里实现，还有为什么我使用 select identity( int,convert(int,substring(convert(char,getdate(),112),1,6)+'0001')-10000 ,1) into temp111 为什么不可以？？
convert(int,substring(convert(char(8),getdate(),112),1,6)+'0001')-10000
+number
from master..spt_values
where type='p'
and number>=0
-----------
2010000001
2010000002
2010000003
2010000004
2010000005
2010000006
2010000007
2010000008
2010000009
2010000010
2010000011
2010000012
2010000013
2010000014
2010000015
2010000016
2010000017
2010000018
2010000019
2010000020
2010000021
2010000022
2010000023
2010000024
2010000025
2010000026
2010000027
2010000028
2010000029
2010000030
2010000031
2010000032
2010000033
2010000034
2010000035
2010000036
2010000037
2010000038
2010000039
2010000040
2010000041
2010000042
2010000043
2010000044
2010000045
2010000046
2010000047
2010000048
2010000049
2010000050
2010000051
2010000052
2010000053
2010000054
2010000055
2010000056
2010000057
2010000058
2010000059
2010000060
2010000061
2010000062
2010000063
2010000064
2010000065
2010000066
2010000067
2010000068
2010000069
2010000070
2010000071
2010000072
2010000073
2010000074
2010000075
2010000076
2010000077
2010000078
2010000079
2010000080
2010000081
2010000082
2010000083
2010000084
2010000085
2010000086
2010000087
2010000088
2010000089
2010000090
2010000091
2010000092
2010000093
2010000094
2010000095
2010000096
2010000097
2010000098
2010000099
2010000100
2010000101
2010000102
2010000103
2010000104
2010000105
2010000106
2010000107
2010000108
2010000109
2010000110
2010000111
2010000112
2010000113
2010000114
2010000115
2010000116
2010000117
2010000118
2010000119
2010000120
2010000121
2010000122
2010000123
2010000124
2010000125
2010000126
2010000127
2010000128
2010000129
2010000130
2010000131
2010000132
2010000133
2010000134
2010000135
2010000136
2010000137
2010000138
2010000139
2010000140
2010000141
2010000142
2010000143
2010000144
2010000145
2010000146
2010000147
2010000148
2010000149
2010000150
2010000151
2010000152
2010000153
2010000154
2010000155
2010000156
2010000157
2010000158
2010000159
2010000160
2010000161
2010000162
2010000163
2010000164
2010000165
2010000166
2010000167
2010000168
2010000169
2010000170
2010000171
2010000172
2010000173
2010000174
2010000175
2010000176
2010000177
2010000178
2010000179
2010000180
2010000181
2010000182
2010000183
2010000184
2010000185
2010000186
2010000187
2010000188
2010000189
2010000190
2010000191
2010000192
2010000193
2010000194
2010000195
2010000196
2010000197
2010000198
2010000199
2010000200
2010000201
2010000202
2010000203
2010000204
2010000205
2010000206
2010000207
2010000208
2010000209
2010000210
2010000211
2010000212
2010000213
2010000214
2010000215
2010000216
2010000217
2010000218
2010000219
2010000220
2010000221
2010000222
2010000223
2010000224
2010000225
2010000226
2010000227
2010000228
2010000229
2010000230
2010000231
2010000232
2010000233
2010000234
2010000235
2010000236
2010000237
2010000238
2010000239
2010000240
2010000241
2010000242
2010000243
2010000244
2010000245
2010000246
2010000247
2010000248
2010000249
2010000250
2010000251
2010000252
2010000253
2010000254
2010000255
2010000256（所影响的行数为 256 行）这样就行了,不过你要利用临时表来处理,这样最大的不大
cast(substring(convert(char(8),getdate(),112),1,6)+'0001' as int)-10000+number
from
master..spt_values
where
type='p'
and
number>=0
select identity(int,2009100001,1) as batchno,billbody.tr_proj as ue_code,'01' as batype,'3001' as deptno,billbody.tr_proj as objectno,'01'as flowno,'11'as ca_mon,'2009-11-01'as bdate,'2009-11-30'as edate,'0'as tsign,'0'as chksign,'0'as comsign,'9999'as inm,
sum(billbody.qty_a) as qty_outp,billbody.tr_proj as projno,'0003'as ocode into temp9999
from billbody left join billhead on billbody.transid=billhead.transid
where billhead.uyear='2009' and billhead.accper='11' and billhead.warehouse='C' group by billbody.tr_proj
现在把这个identity(int,201000001,1)换作你写的，是这样么
cast(substring(convert(char(8),getdate(),112),1,6)+'0001' as int)-10000+number AS batchno,
billbody.tr_proj as ue_code,'01' as batype,'3001' as deptno,
billbody.tr_proj as objectno,'01'as flowno,'11'as ca_mon,'2009-11-01'as bdate,'2009-11-30'as edate,'0'as tsign,'0'as chksign,'0'as comsign,'9999'as inm, sum(billbody.qty_a) as qty_outp,
billbody.tr_proj as projno,
'0003'as ocode
from
billbody left join billhead on billbody.transid=billhead.transid LEFT JOIN MASTER..SPT_VALUES
WHERE TYPE='P' AND NUMBER>0 AND
billhead.uyear='2009'
and billhead.accper='11'
and billhead.warehouse='C' 你的统计分组不正确吧,晕,这样先,统计
消息 156，级别 15，状态 1，第 11 行
关键字 'WHERE' 附近有语法错误。
呵呵，错误
ON TYPE='P' AND NUMBER>0 AND
billhead.uyear='2009'
and billhead.accper='11'
and billhead.warehouse='C' 把那个WHERE 改成ON