/* 第一批 a,b,c第二批 d,e,f */ select row_number() over (order by (select 1))as rid,* from ( select 'a' as c1 union select 'b' union select 'c' ) a union all select row_number() over (order by (select 1))as rid,* from ( select 'd' as c1 union select 'e' union select 'f' ) b/* rid c1 -------------------- ---- 1 a 2 b 3 c 1 d 2 e 3 f */ 也可以每次插入的时候加入一个批次号,然后用批次号分组后排序!!
怎么操作呢?能详细一些吗?? "用row_number生成序号后插入"。
我觉得批次号更容易些:if object_id('[guuxy_G]') is not null drop table [guuxy_G]create table [guuxy_G] (c1 varchar(4),c2 varchar(4),c3 varchar(4),[sign] uniqueidentifier) --插入第一批数据 insert into [guuxy_G] select 'a','b','c',newid()declare @t uniqueidentifier set @t=newid() --插入第二批数据 insert into [guuxy_G] select 'd','e','f',@t union all select 'g','i','j',@t union all select 'k','l','m',@t set @t=newid() --插入第三批数据insert into [guuxy_G] select 'n','o','p',@t union all select 'q','r','s',@t union all select 't','u','v',@t union all select 'w','x','y',@t select row_number() over (partition by [sign] order by getdate()) as id,c1,c2,c3 from [guuxy_G] /* id c1 c2 c3 -------------------- ---- ---- ---- 1 d e f 2 g i j 3 k l m 1 n o p 2 q r s 3 t u v 4 w x y 1 a b c */
我已经: select OrderDetailsID ,ROW_NUMBER()OVER (ORDER BY OrderDetailsID) AS OrderDetailsSerials FROM Order_1Details ORDER BY OrderDetailsID查询时可以看见, 但是表中字段 OrderDetailsSerials无法发生成序号
再新建一个数据,把新来的一批数据,用row_number生成序号后插入。
/*
第一批
a,b,c第二批
d,e,f
*/
select row_number() over (order by (select 1))as rid,*
from
(
select 'a' as c1 union
select 'b' union
select 'c'
) a
union all
select row_number() over (order by (select 1))as rid,*
from
(
select 'd' as c1 union
select 'e' union
select 'f'
) b/*
rid c1
-------------------- ----
1 a
2 b
3 c
1 d
2 e
3 f
*/
也可以每次插入的时候加入一个批次号,然后用批次号分组后排序!!
怎么操作呢?能详细一些吗??
"用row_number生成序号后插入"。
drop table [guuxy_G]create table [guuxy_G]
(c1 varchar(4),c2 varchar(4),c3 varchar(4),[sign] uniqueidentifier)
--插入第一批数据
insert into [guuxy_G]
select 'a','b','c',newid()declare @t uniqueidentifier set @t=newid()
--插入第二批数据
insert into [guuxy_G]
select 'd','e','f',@t union all
select 'g','i','j',@t union all
select 'k','l','m',@t set @t=newid()
--插入第三批数据insert into [guuxy_G]
select 'n','o','p',@t union all
select 'q','r','s',@t union all
select 't','u','v',@t union all
select 'w','x','y',@t select row_number() over (partition by [sign] order by getdate())
as id,c1,c2,c3 from [guuxy_G]
/*
id c1 c2 c3
-------------------- ---- ---- ----
1 d e f
2 g i j
3 k l m
1 n o p
2 q r s
3 t u v
4 w x y
1 a b c
*/
select
OrderDetailsID
,ROW_NUMBER()OVER (ORDER BY OrderDetailsID) AS OrderDetailsSerials
FROM Order_1Details
ORDER BY OrderDetailsID查询时可以看见,
但是表中字段 OrderDetailsSerials无法发生成序号
这个updata 怎么写??