if object_id('pubs..tb') is not null drop table tb gocreate table tb ( a varchar(10), b int, tdate datetime )insert into tb(a,b,tdate) values('1', 1, '2001-7-7') insert into tb(a,b,tdate) values('2', 1, '2005-10-10') insert into tb(a,b,tdate) values('2', 4, '2004-1-17') insert into tb(a,b,tdate) values('2', 3, '2009-12-1') insert into tb(a,b,tdate) values('3', 9, '2001-10-8') insert into tb(a,b,tdate) values('3', 2, '2003-10-24') insert into tb(a,b,tdate) values('4', 3, '2007-12-31') insert into tb(a,b,tdate) values('4', 8, '2001-4-9') insert into tb(a,b,tdate) values('4', 9, '2005-3-27') insert into tb(a,b,tdate) values('4', 5, '2005-6-9') insert into tb(a,b,tdate) values('4', 7, '2003-3-8')select a,b=(select count(1) from tb where a=a.a and tdate<a.tdate)+1 , tdate from tb a order by a,bdrop table tb a b tdate ---------- ----------- ------------------------------------------------------ 1 1 2001-07-07 00:00:00.000 2 1 2004-01-17 00:00:00.000 2 2 2005-10-10 00:00:00.000 2 3 2009-12-01 00:00:00.000 3 1 2001-10-08 00:00:00.000 3 2 2003-10-24 00:00:00.000 4 1 2001-04-09 00:00:00.000 4 2 2003-03-08 00:00:00.000 4 3 2005-03-27 00:00:00.000 4 4 2005-06-09 00:00:00.000 4 5 2007-12-31 00:00:00.000(所影响的行数为 11 行)
能不能解释下 一楼 的句子?刚起步,看不懂...这个可以改b的序号,但是不能按照tdate来排列。
三楼dawugui的语句,我怎么想怎么写也转换不成Update语句,能否给出?
update table set b=(select count(*) from table tb where tb.a=table.a and tb.b<=table.b)
update ht02 set a_id=(select count(1) from ht02 a where a0188=a.a0188 and edate<a.edate)+1 报错update ht02 set a_id=(select count(*) from ht02 tb where tb.a0188=ht02.a0188 and tb.a_id<=ht02.a_id ) 不报错
drop table tb
gocreate table tb
(
a varchar(10),
b int,
tdate datetime
)insert into tb(a,b,tdate) values('1', 1, '2001-7-7')
insert into tb(a,b,tdate) values('2', 1, '2005-10-10')
insert into tb(a,b,tdate) values('2', 4, '2004-1-17')
insert into tb(a,b,tdate) values('2', 3, '2009-12-1')
insert into tb(a,b,tdate) values('3', 9, '2001-10-8')
insert into tb(a,b,tdate) values('3', 2, '2003-10-24')
insert into tb(a,b,tdate) values('4', 3, '2007-12-31')
insert into tb(a,b,tdate) values('4', 8, '2001-4-9')
insert into tb(a,b,tdate) values('4', 9, '2005-3-27')
insert into tb(a,b,tdate) values('4', 5, '2005-6-9')
insert into tb(a,b,tdate) values('4', 7, '2003-3-8')select a,b=(select count(1) from tb where a=a.a and tdate<a.tdate)+1 , tdate from tb a order by a,bdrop table tb
a b tdate
---------- ----------- ------------------------------------------------------
1 1 2001-07-07 00:00:00.000
2 1 2004-01-17 00:00:00.000
2 2 2005-10-10 00:00:00.000
2 3 2009-12-01 00:00:00.000
3 1 2001-10-08 00:00:00.000
3 2 2003-10-24 00:00:00.000
4 1 2001-04-09 00:00:00.000
4 2 2003-03-08 00:00:00.000
4 3 2005-03-27 00:00:00.000
4 4 2005-06-09 00:00:00.000
4 5 2007-12-31 00:00:00.000(所影响的行数为 11 行)
update ht02 set a_id=(select count(1) from ht02 a where a0188=a.a0188 and edate<a.edate)+1
报错update ht02 set a_id=(select count(*) from ht02 tb
where tb.a0188=ht02.a0188 and tb.a_id<=ht02.a_id )
不报错