有数据表 table1 数据如下
id date time
0 2007-11-01 16:17:31
3 2007-11-01 12:01:43
3 2007-11-01 14:51:04
3 2007-11-02 10:51:09
3 2007-11-02 16:14:12
3 2007-11-03 11:10:06
7 2007-11-01 12:01:47
7 2007-11-05 10:39:47
7 2007-11-05 11:14:55
14 2007-11-01 14:20:58
14 2007-11-01 16:48:01
14 2007-11-02 13:54:39
要变成如下
no id date time
1 0 2007-11-01 16:17:31
1 3 2007-11-01 12:01:43
2 3 2007-11-01 14:51:04
3 3 2007-11-02 10:51:09
4 3 2007-11-02 16:14:12
5 3 2007-11-03 11:10:06
1 7 2007-11-01 12:01:47
2 7 2007-11-05 10:39:47
3 7 2007-11-05 11:14:55
1 14 2007-11-01 14:20:58
2 14 2007-11-01 16:48:01
3 14 2007-11-02 13:54:39
利用sql语句实现或者其他方法都可以,,多谢
id date time
0 2007-11-01 16:17:31
3 2007-11-01 12:01:43
3 2007-11-01 14:51:04
3 2007-11-02 10:51:09
3 2007-11-02 16:14:12
3 2007-11-03 11:10:06
7 2007-11-01 12:01:47
7 2007-11-05 10:39:47
7 2007-11-05 11:14:55
14 2007-11-01 14:20:58
14 2007-11-01 16:48:01
14 2007-11-02 13:54:39
要变成如下
no id date time
1 0 2007-11-01 16:17:31
1 3 2007-11-01 12:01:43
2 3 2007-11-01 14:51:04
3 3 2007-11-02 10:51:09
4 3 2007-11-02 16:14:12
5 3 2007-11-03 11:10:06
1 7 2007-11-01 12:01:47
2 7 2007-11-05 10:39:47
3 7 2007-11-05 11:14:55
1 14 2007-11-01 14:20:58
2 14 2007-11-01 16:48:01
3 14 2007-11-02 13:54:39
利用sql语句实现或者其他方法都可以,,多谢
解决方案 »
- 关于BHO的问题
- 调用idsmtp的connect方法时为什么报错: Send HELO/EHLO first
- 高分讨论Delphi通过Web Services编写的CGI问题,通过SOAP协议调用CGI函数问题,分不够再加
- 给大家推荐一个关于三层开发的好帖子
- delphi中使用控件操作excel表格,做过的朋友进来看看。
- 各位请来讨论一下一个多机联网监控程序的解决方案。
- 如何是否有某程序在运行并且关闭它
- DBGrid中如何实现title标题字段栏的字段双(多)行显示?
- 我从MSSQLSERVER上COPY一个DB到C:\打开时汉字显示####,我该如何解决
- 如何将MP4格式转换成AVI格式,谢谢
- 关于释放指针的问题,有点怪,
- 怎么定时在一天的某个时间自动运行某段代码?
1 0 2007-11-01 16:17:31
1 3 2007-11-01 12:01:43
2 3 2007-11-01 14:51:04
1 3 2007-11-02 10:51:09
2 3 2007-11-02 16:14:12
1 3 2007-11-03 11:10:06
1 7 2007-11-01 12:01:47
1 7 2007-11-05 10:39:47
2 7 2007-11-05 11:14:55
1 14 2007-11-01 14:20:58
2 14 2007-11-01 16:48:01
1 14 2007-11-02 13:54:39 同一天,同ID的分类增加前面序号
insert into table1 values('0', '2007-11-01','16:17:31')
insert into table1 values('3', '2007-11-01','12:01:43')
insert into table1 values('3', '2007-11-01','14:51:04')
insert into table1 values('3', '2007-11-02','10:51:09')
insert into table1 values('3', '2007-11-02','16:14:12')
insert into table1 values('3', '2007-11-03','11:10:06')
insert into table1 values('7', '2007-11-01','12:01:47')
insert into table1 values('7', '2007-11-05','10:39:47')
insert into table1 values('7', '2007-11-05','11:14:55')
insert into table1 values('14', '2007-11-01','14:20:58')
insert into table1 values('14', '2007-11-01','16:48:01')
insert into table1 values('14', '2007-11-02','13:54:39')
*/select top 20000 identity(int,1,1) as num,* into # from table1select (select count(*) from # as t1 where t1.num<=t0.num and t1.[date]=t0.[date] and t1.[id]=t0.[id] ) as [no],
[id],
[date],
[time]
from # as t0drop table #