表已经存在，数据也有了，增加一个字段给付值

[julong88]

表
ID Name SellNum
1 XX 001
2 YY 001
3 FF 002
4 DD 002

现在增加一个字段

ID Name SellNum XuHao
1 XX 001 1
2 YY 001 2
3 FF 002 1
4 DD 002 2

新增字段值怎么付？

[gogodiy]

alter table 表 drop column 列

[中国风]

alter table Tab1e add XuHao int


UPDATE a
SET XuHao =row
FROM (SELECT *,row=ROW_NUMBER()OVER(PARTITION BY SellNum ORDER BY ID) FROM table1) AS a

[水族杰纶]

alter table tb
add xuhao int
go
;with tt 
as
(select *,
 px=row_number() over(partition by SellNum order by id)
from tb)
update tt
set xuhao=px

[中国风]

新增字段再更新

[NBDBA]

利用row_number()生成序号，懒得写了

[中国风]

UPDATE b
SET XuHao =a.row
FROM (SELECT ID,(SELECT COUNT(1) FROM table1 WHERE SellNum=a.SellNum AND ID<=a.ID) AS row FROM table1 AS a)a
INNER JOIN table1 AS b ON a.ID=b.ID

SQL2000可用這個更新

[--小F--]

update
  a
set
  XuHao=px
from
  a,
  (select *,px=row_number()over(partition by SellNum order by id) from tb)b
where
  a.id=b.id and a.name=b.name

[dawugui]

alter table 表 add XuHao int
go

update 表 set xuhao = (select count(1) from 表 where SellNum = t.SellNum and id < t.id) + 1 from 表 t

[SomethingJack]

[Quote=引用 7 楼 dawugui 的回复:]

alter table 表 add XuHao int
go

update 表 set xuhao = (select count(1) from 表 where SellNum = t.SellNum and id < t.id) + 1 from 表 t
[/Quote]
那如何删除一个字段呢？？