if object_id('tempdb..#')is not null drop table # go create table #(单号 varchar(10), 客户 varchar(10), 序号 varchar(10)) insert # select 'A2008001' , 'A2008','' insert # select 'A2008001' , 'A2007','' insert # select 'A2008001' , 'A2006','' insert # select 'A2008002' , 'A2009','' insert # select 'A2008002' , 'A2009','' alter table # add id int identity go update # set 序号=(select count(*)+1 from # where 单号 =t.单号 and ID<t.ID) from # t alter table # drop column id go select * from # /*单号 客户 序号 ---------- ---------- ---------- A2008001 A2008 1 A2008001 A2007 2 A2008001 A2006 3 A2008002 A2009 1 A2008002 A2009 2 */
UPDATE A SET 序号 = (SELECT COUNT(*) FROM tb B WHERE A.单号 = B.单号 AND A.序号 >= B.序号) FROM tb A
-- 前面写错一个列 UPDATE A SET 序号 = (SELECT COUNT(*) FROM tb B WHERE A.单号 = B.单号 AND A.客户 >= B.客户) FROM tb A-- SQL 2005 还可以用 ;WITH DATA AS( SELECT *, 序号1 = ROW_NUMBER() OVER(PARTITION BY 单号 ORDER BY 客户) FROM tb ) UPDATE DATA SET 序号 = 序号1
SELECT *, ROW_NUMBER() OVER(order BY 单号 desc) as 序号 FROM tbsql2005提供了一个 ROW_NUMBER() 函数可以用
SELECT *, ROW_NUMBER()OVER(partition by 单号 order BY 单号) as 序号 FROM tb
go
create table #(单号 varchar(10), 客户 varchar(10), 序号 varchar(10))
insert # select 'A2008001' , 'A2008',''
insert # select 'A2008001' , 'A2007',''
insert # select 'A2008001' , 'A2006',''
insert # select 'A2008002' , 'A2009',''
insert # select 'A2008002' , 'A2009',''
alter table #
add id int identity
go
update # set 序号=(select count(*)+1 from # where 单号 =t.单号 and ID<t.ID) from # t
alter table #
drop column id
go
select * from #
/*单号 客户 序号
---------- ---------- ----------
A2008001 A2008 1
A2008001 A2007 2
A2008001 A2006 3
A2008002 A2009 1
A2008002 A2009 2
*/
序号 = (SELECT COUNT(*) FROM tb B WHERE A.单号 = B.单号 AND A.序号 >= B.序号)
FROM tb A
-- 前面写错一个列
UPDATE A SET
序号 = (SELECT COUNT(*) FROM tb B WHERE A.单号 = B.单号 AND A.客户 >= B.客户)
FROM tb A-- SQL 2005 还可以用
;WITH
DATA AS(
SELECT
*,
序号1 = ROW_NUMBER() OVER(PARTITION BY 单号 ORDER BY 客户)
FROM tb
)
UPDATE DATA SET
序号 = 序号1
FROM tbsql2005提供了一个 ROW_NUMBER() 函数可以用
FROM tb