table:
name date times
a 2003.01.01
b 2003.01.02
a 2003.01.03
a 2003.01.04
c 2003.01.05
c 2003.01.06
a 2003.01.07
b 2003.01.08
我要根据时间的先后顺序填写各个字母times(次数)的数字
该如何写这个sql,没有思路阿?
填完后的表是:
name date times
a 2003.01.01 1
b 2003.01.02 1
a 2003.01.03 2
a 2003.01.04 3
c 2003.01.05 1
c 2003.01.06 2
a 2003.01.07 4
b 2003.01.08 2
name date times
a 2003.01.01
b 2003.01.02
a 2003.01.03
a 2003.01.04
c 2003.01.05
c 2003.01.06
a 2003.01.07
b 2003.01.08
我要根据时间的先后顺序填写各个字母times(次数)的数字
该如何写这个sql,没有思路阿?
填完后的表是:
name date times
a 2003.01.01 1
b 2003.01.02 1
a 2003.01.03 2
a 2003.01.04 3
c 2003.01.05 1
c 2003.01.06 2
a 2003.01.07 4
b 2003.01.08 2
解决方案 »
- 基表中创建索引,对视图有影响吗?
- url引用sql reporting services的时候能否给报表传一个参数?怎么弄?
- ADO + SQL Server 2000 事务嵌套报错是怎么回事?
- 关于多级分类的显示:应如何写sql.在线等..
- 。。。。。。。如何使用存储过程多参数查询?
- sql 基本问题:如何 创建和删除数据库。
- 远程连接sql2000已启用1433,但连接速度超慢,急求原因和解决方法
- msdn上的描述的不太懂,麻烦大虾解释下
- 请教一个sql语句。比较急,请高手指教
- 关于相关子查询的问题,还望各位兄弟姐妹帮帮忙,一直在线
- 莱鸟问题啊,
- DTS及SQL server 2005与SQL server 2000转换问题(在线等待)
insert into @tb select 'a','2003.01.01',null
insert into @tb select 'b','2003.01.02',null
insert into @tb select 'a','2003.01.03',null
insert into @tb select 'a','2003.01.04',nullinsert into @tb select 'c','2003.01.05',null
insert into @tb select 'c','2003.01.06',null
insert into @tb select 'a','2003.01.07',null
insert into @tb select 'b','2003.01.08',nullupdate @tb set times=(select count(1) from @tb where name=t.name and date<=t.date) from @tb tselect * from @tb
name date times
a 2003-01-01 00:00:00.000 1
b 2003-01-02 00:00:00.000 1
a 2003-01-03 00:00:00.000 2
a 2003-01-04 00:00:00.000 3
c 2003-01-05 00:00:00.000 1
c 2003-01-06 00:00:00.000 2
a 2003-01-07 00:00:00.000 4
b 2003-01-08 00:00:00.000 2
insert into @tb select 'a','2003.01.01',null
insert into @tb select 'b','2003.01.02',null
insert into @tb select 'a','2003.01.03',null
insert into @tb select 'a','2003.01.04',nullinsert into @tb select 'c','2003.01.05',null
insert into @tb select 'c','2003.01.06',null
insert into @tb select 'a','2003.01.07',null
insert into @tb select 'b','2003.01.08',nullupdate @tb set times=(select count(1) from @tb where name=t.name and date<=t.date) from @tb tselect name,convert(varchar(10),date,120) as date,times from @tbname date times
a 2003-01-01 1
b 2003-01-02 1
a 2003-01-03 2
a 2003-01-04 3
c 2003-01-05 1
c 2003-01-06 2
a 2003-01-07 4
b 2003-01-08 2
times=(select count(*) from T where name=A.name and [date]<=A.[date])
from T as A
from table a,table b
where a.name = b.name and a.date>=b.date
group by a.name,a.date
insert into tb select 'a','2003.01.01',null
insert into tb select 'b','2003.01.02',null
insert into tb select 'a','2003.01.03',null
insert into tb select 'a','2003.01.04',null
insert into tb select 'c','2003.01.05',null
insert into tb select 'c','2003.01.06',null
insert into tb select 'a','2003.01.07',null
insert into tb select 'b','2003.01.08',null
goselect name , date , times = (select count(1) from tb where name = t.name and date < t.date) + 1 from tb t drop table tb/*
name date times
---- ------------------------------------------------------ -----------
a 2003-01-01 00:00:00.000 1
b 2003-01-02 00:00:00.000 1
a 2003-01-03 00:00:00.000 2
a 2003-01-04 00:00:00.000 3
c 2003-01-05 00:00:00.000 1
c 2003-01-06 00:00:00.000 2
a 2003-01-07 00:00:00.000 4
b 2003-01-08 00:00:00.000 2(所影响的行数为 8 行)
*/
select name,[date],times=(select count(*) from [table] where name=t.name and [date]<=t.[date])
from [table] t
insert into @tb select 'a','2003.01.01',null
insert into @tb select 'a','2003.01.01',null
insert into @tb select 'a','2003.01.01',null
insert into @tb select 'a','2003.01.01',null
insert into @tb select 'a','2003.01.01',null
insert into @tb select 'b','2003.01.02',null
insert into @tb select 'a','2003.01.03',null
insert into @tb select 'a','2003.01.04',nullinsert into @tb select 'c','2003.01.05',null
insert into @tb select 'c','2003.01.06',null
insert into @tb select 'a','2003.01.07',null
insert into @tb select 'b','2003.01.08',nullselect [name],[date],
times=(select count(*) from @tb where name=A.name and [date] <=A.[date])
from @tb as A
/*
name date times
---- ------------------------------------------------------ -----------
a 2003-01-01 00:00:00.000 5
a 2003-01-01 00:00:00.000 5
a 2003-01-01 00:00:00.000 5
a 2003-01-01 00:00:00.000 5
a 2003-01-01 00:00:00.000 5
b 2003-01-02 00:00:00.000 1
a 2003-01-03 00:00:00.000 6
a 2003-01-04 00:00:00.000 7
c 2003-01-05 00:00:00.000 1
c 2003-01-06 00:00:00.000 2
a 2003-01-07 00:00:00.000 8
b 2003-01-08 00:00:00.000 2
*/
insert into @tb select 'a','2003.01.01',null
insert into @tb select 'a','2003.01.01',null
insert into @tb select 'a','2003.01.01',null
insert into @tb select 'a','2003.01.01',null
insert into @tb select 'a','2003.01.01',null
insert into @tb select 'b','2003.01.02',null
insert into @tb select 'a','2003.01.03',null
insert into @tb select 'a','2003.01.04',nullinsert into @tb select 'c','2003.01.05',null
insert into @tb select 'c','2003.01.06',null
insert into @tb select 'a','2003.01.07',null
insert into @tb select 'b','2003.01.08',nulldeclare @i int
set @i = 0
update @tb set times = @i,@i=@i+1
update @tb set times=(select count(*)+1 from @tb where name=A.name and ([date] <A.[date] or (date = a.date and times <a.times)))
from @tb as A
/*
name date times
---- ------------------------------------------------------ -----------
a 2003-01-01 00:00:00.000 1
a 2003-01-01 00:00:00.000 2
a 2003-01-01 00:00:00.000 3
a 2003-01-01 00:00:00.000 4
a 2003-01-01 00:00:00.000 5
b 2003-01-02 00:00:00.000 1
a 2003-01-03 00:00:00.000 6
a 2003-01-04 00:00:00.000 7
c 2003-01-05 00:00:00.000 1
c 2003-01-06 00:00:00.000 2
a 2003-01-07 00:00:00.000 8
b 2003-01-08 00:00:00.000 2
*/
select * from @tb
update @tb set times=(select count(9) from @tb where name=a.name and data<=a.data ) from @tb a
select * from @tb