update table t set [序号]=(select count(1)+1 from table where [编号]<t.[编号] and [日期]=t.[日期])
create table t1 ( 编号 char(3), 日期 char(6),
) insert into t1 select '001', '050916' union select '002', '050916' union select '003', '050916' union select '004', '050917' union select '005', '050917' union select '006', '050918'select 编号,日期, 序号=(select count(*) from t1 where 日期=a.日期 and 编号<=a.编号) from t1 a 编号 日期 序号 ---- ------ ----------- 001 050916 1 002 050916 2 003 050916 3 004 050917 1 005 050917 2 006 050918 1(所影响的行数为 6 行)
create table nn_test(编号 varchar(20),日期 varchar(20),序号 int) insert into nn_test select '001','050916',null union all select '002','050916',null union all select '003','050916',null union all select '004','050917',null union all select '005','050917',null union all select '006','050918',null create proc n_test as begin select 编号,日期,(select count(*) from nn_test where 日期=nn.日期 and 编号<=nn.编号 ) as 序号 from nn_test nn order by 编号 end
update table t set [序号]=(select count(1)+1 from table where [编号]<t.[编号] and [日期]=t.[日期])
(
编号 char(3),
日期 char(6),
)
insert into t1
select '001', '050916'
union
select '002', '050916'
union
select '003', '050916'
union
select '004', '050917'
union
select '005', '050917'
union
select '006', '050918'select 编号,日期, 序号=(select count(*) from t1 where 日期=a.日期 and 编号<=a.编号) from t1 a 编号 日期 序号
---- ------ -----------
001 050916 1
002 050916 2
003 050916 3
004 050917 1
005 050917 2
006 050918 1(所影响的行数为 6 行)
insert into nn_test
select '001','050916',null
union all
select '002','050916',null
union all
select '003','050916',null
union all
select '004','050917',null
union all
select '005','050917',null
union all
select '006','050918',null
create proc n_test
as
begin
select 编号,日期,(select count(*) from nn_test where 日期=nn.日期 and 编号<=nn.编号 ) as 序号
from nn_test nn order by 编号
end
vivianfdlpw() 和gimy007(逮猫的耗子) 的方法,我调试了一下,都不成功!
hsj20041004(光芒) 和xq02(刚刚)的方法只是用来查询,并不能修改数据库的序号列
请赐教!谢谢
update table set cno=(select count(ID2) from table table1 where 登记日期=table.登记日期)
在SQL server2000调试成功
create table nn_test(ID2 varchar(20),登记日期 varchar(20),cno int)
insert into nn_test
select '001','050916',null
union all
select '002','050916',null
union all
select '003','050916',null
union all
select '004','050917',null
union all
select '005','050917',null
union all
select '006','050918',null
update nn_test set cno=(select count(ID2) from nn_test table1 where 登记日期=nn_test.登记日期)
select * from nn_test
--结果
ID2 登记日期 cno
-------------------- -------------------- -----------
001 050916 3
002 050916 3
003 050916 3
004 050917 2
005 050917 2
006 050918 1(所影响的行数为 6 行)
update nn_test set cno=(select count(1) from nn_test where 登记日期=A.登记日期 AND ID2<=A.ID2) FROM nn_test A
select * from nn_test
--结果
ID2 登记日期 cno
-------------------- -------------------- -----------
001 050916 1
002 050916 2
003 050916 3
004 050917 1
005 050917 2
006 050918 1(所影响的行数为 6 行)