求一句Sql，按照日期自动生成顺序号

Question

saleTable(销售表)：
ID          SALEDATE
TS00000001 2006-12-30 23:39:27
TS00000058 2007-1-2 0:45:15
TS00000070 2007-1-6 23:01:04
TS00000071 2007-1-6 23:01:08
TS00000073 2007-1-6 23:16:59
TS00000109 2007-1-11 22:44:52
TS00000110 2007-1-11 22:50:55
TS00000111 2007-1-11 22:58:21
TS00000112 2007-1-11 23:50:41
.......         .................要增加一个顺序号列(rownumber)，按照当天的销售时间先后顺序从1开始依次累加，如下效果：
ID          SALEDATE              rownumber
TS00000001 2006-12-30 23:39:27        1
TS00000058 2007-1-2 0:45:15           1
TS00000070 2007-1-6 23:01:04          1 
TS00000071 2007-1-6 23:01:08          2
TS00000073 2007-1-6 23:16:59          3
TS00000109 2007-1-11 22:44:52         1
TS00000110 2007-1-11 22:50:55         2
TS00000111 2007-1-11 22:58:21         3
TS00000112 2007-1-11 23:50:41         4--------------------------------------------------------
请各位高手献技啦！！！！！！

Answer 1

select * from saleTable
order by saledate asc ,rownumber asc

Answer 2

declare @tb table (id varchar(20),saledate datetime)
insert into @tb select 'TS00000001','2006-12-30 23:39:27'
insert into @tb select 'TS00000058','2007-1-2   0:45:15'
insert into @tb select 'TS00000070','2007-1-6   23:01:04'
insert into @tb select 'TS00000071','2007-1-6   23:01:08'
insert into @tb select 'TS00000073','2007-1-6   23:16:59'
insert into @tb select 'TS00000109','2007-1-11   22:44:52'
insert into @tb select 'TS00000110','2007-1-11   22:50:55'
insert into @tb select 'TS00000111','2007-1-11   22:58:21'
insert into @tb select 'TS00000112','2007-1-11   23:50:41'select *,
px=(select count(1)+1 from @tb where convert(varchar(10),saledate,120)=convert(varchar(10),a.saledate,120)
and saledate<=a.saledate and checksum(saledate) <>checksum(a.saledate)
) from @tb aid saledate px
TS00000001 2006-12-30 23:39:27.000 1
TS00000058 2007-01-02 00:45:15.000 1
TS00000070 2007-01-06 23:01:04.000 1
TS00000071 2007-01-06 23:01:08.000 2
TS00000073 2007-01-06 23:16:59.000 3
TS00000109 2007-01-11 22:44:52.000 1
TS00000110 2007-01-11 22:50:55.000 2
TS00000111 2007-01-11 22:58:21.000 3
TS00000112 2007-01-11 23:50:41.000 4呵呵 蒙出来了

Answer 3

declare @tb table (id varchar(20),saledate datetime)
insert into @tb select 'TS00000001','2006-12-30 23:39:27'
insert into @tb select 'TS00000058','2007-1-2   0:45:15'
insert into @tb select 'TS00000070','2007-1-6   23:01:04'
insert into @tb select 'TS00000071','2007-1-6   23:01:08'
insert into @tb select 'TS00000073','2007-1-6   23:16:59'
insert into @tb select 'TS00000109','2007-1-11   22:44:52'
insert into @tb select 'TS00000110','2007-1-11   22:50:55'
insert into @tb select 'TS00000111','2007-1-11   22:58:21'
insert into @tb select 'TS00000112','2007-1-11   23:50:41'select *,
px=(select count(1) from @tb where convert(varchar(10),saledate,120)=convert(varchar(10),a.saledate,120)
and saledate<=a.saledate 
) from @tb aid saledate px
TS00000001 2006-12-30 23:39:27.000 1
TS00000058 2007-01-02 00:45:15.000 1
TS00000070 2007-01-06 23:01:04.000 1
TS00000071 2007-01-06 23:01:08.000 2
TS00000073 2007-01-06 23:16:59.000 3
TS00000109 2007-01-11 22:44:52.000 1
TS00000110 2007-01-11 22:50:55.000 2
TS00000111 2007-01-11 22:58:21.000 3
TS00000112 2007-01-11 23:50:41.000 4

Answer 4

create table #t (id varchar(20),saledate datetime)
insert into #t values('TS00000001','2006-12-30   23:39:27')
insert into #t values('TS00000058','2007-1-2   0:45:15')
insert into #t values('TS00000070','2007-1-6   23:01:04')
insert into #t values('TS00000071','2007-1-6   23:01:08')
insert into #t values('TS00000073','2007-1-6   23:16:59')
insert into #t values('TS00000109','2007-1-11   22:44:52')
insert into #t values('TS00000110','2007-1-11   22:50:55')
insert into #t values('TS00000111','2007-1-11   22:58:21')
insert into #t values('TS00000112','2007-1-11   23:50:41')select a.*,rownumber=(select count(1) from #t where convert(varchar(10),saledate,110)=convert(varchar(10),a.saledate,110) and saledate<=a.saledate) from #t a
id                   saledate                rownumber
-------------------- ----------------------- -----------
TS00000001           2006-12-30 23:39:27.000 1
TS00000058           2007-01-02 00:45:15.000 1
TS00000070           2007-01-06 23:01:04.000 1
TS00000071           2007-01-06 23:01:08.000 2
TS00000073           2007-01-06 23:16:59.000 3
TS00000109           2007-01-11 22:44:52.000 1
TS00000110           2007-01-11 22:50:55.000 2
TS00000111           2007-01-11 22:58:21.000 3
TS00000112           2007-01-11 23:50:41.000 4

Answer 5

saleTable(销售表)： 
ID               SALEDATE                     rownumber 
TS00000001 2006-12-30   23:39:27                 1 
TS00000058 2007-1-2   0:45:15                       1 
TS00000070 2007-1-6   23:01:04                     1   
TS00000071 2007-1-6   23:01:08                     2 
TS00000073 2007-1-6   23:16:59                     3 
TS00000109 2007-1-11   22:44:52                   1 
TS00000110 2007-1-11   22:50:55                   2 
TS00000111 2007-1-11   22:58:21                   3 
TS00000112 2007-1-11   23:50:41                   4 
-------------------------------------
select *,rownumber=(select count(1) from saleTable where datediff(dd,SALEDATE,a.SALEDATE)=0)
from saleTable a

Answer 6

忘记加1了
select *,rownumber=(select count(1) from saleTable where datediff(dd,SALEDATE,a.SALEDATE)=0)+1
from saleTable a

Answer 7

倒，这么简单的问题错几遍。汗颜`
select *,rownumber=(select count(1) from saleTable where datediff(dd,SALEDATE,a.SALEDATE)=0
and SALEDATE<a.SALEDATE)+1
from saleTable a