请问如何按照时间排列并分类

Question

表结构如下id      title         rdate
------|-------------|---------
1        aaaaa        2007-6-1
2        aaaaa        2007-6-1
3        aaaaa        2007-6-2
4        aaaaa        2007-6-2
5        aaaaa        2007-6-3
6        aaaaa        2007-6-3
7        aaaaa        2007-6-4
8        aaaaa        2007-6-4
9        aaaaa        2007-6-4我想要输出的时候这样按时间分类输出2007-6-1
1        aaaaa        2007-6-1
2        aaaaa        2007-6-12007-6-2
3        aaaaa        2007-6-2
4        aaaaa        2007-6-22007-6-3
5        aaaaa        2007-6-3
6        aaaaa        2007-6-3
....以此类推。。请指教谢谢

Answer 1

select px=(select count(1) from tb where title=a.title and rdate=a.rdate and id < a.id)+1 , * from tb a
order by title,rdate,px

Answer 2

create table tb(id int,title varchar(10),rdate datetime) 
insert into tb values(1,'aaaaa','2007-6-1') 
insert into tb values(2,'aaaaa','2007-6-1') 
insert into tb values(3,'aaaaa','2007-6-2') 
insert into tb values(4,'aaaaa','2007-6-2') 
insert into tb values(5,'aaaaa','2007-6-3') 
insert into tb values(6,'aaaaa','2007-6-3') 
insert into tb values(7,'aaaaa','2007-6-4') 
insert into tb values(8,'aaaaa','2007-6-4') 
insert into tb values(9,'aaaaa','2007-6-4') 
goselect px=(select count(1) from tb where title=a.title and rdate=a.rdate and id < a.id)+1 , * from tb a
order by title,rdate,pxdrop table tb/*
px          id          title      rdate                                                  
----------- ----------- ---------- ------------------------------------------------------ 
1           1           aaaaa      2007-06-01 00:00:00.000
2           2           aaaaa      2007-06-01 00:00:00.000
1           3           aaaaa      2007-06-02 00:00:00.000
2           4           aaaaa      2007-06-02 00:00:00.000
1           5           aaaaa      2007-06-03 00:00:00.000
2           6           aaaaa      2007-06-03 00:00:00.000
1           7           aaaaa      2007-06-04 00:00:00.000
2           8           aaaaa      2007-06-04 00:00:00.000
3           9           aaaaa      2007-06-04 00:00:00.000（所影响的行数为 9 行）
*/

Answer 3

create table tb(id int,title varchar(10),rdate datetime) 
insert into tb values(1,'aaaaa','2007-6-1') 
insert into tb values(2,'aaaaa','2007-6-1') 
insert into tb values(3,'aaaaa','2007-6-2') 
insert into tb values(4,'aaaaa','2007-6-2') 
insert into tb values(5,'aaaaa','2007-6-3') 
insert into tb values(6,'aaaaa','2007-6-3') 
insert into tb values(7,'aaaaa','2007-6-4') 
insert into tb values(8,'aaaaa','2007-6-4') 
insert into tb values(9,'aaaaa','2007-6-4') 
goselect * from 
(
  select distinct convert(varchar(10),rdate,120) id , title = '' , rdate = convert(varchar(10),rdate,120) from tb
  union all
  select cast(id as varchar) id ,title, convert(varchar(10),rdate,120) rdate from tb
) t
order by rdate , case when charindex('-',id) > 0 then 1 else 2 enddrop table tb/*
id                             title      rdate      
------------------------------ ---------- ---------- 
2007-06-01                                2007-06-01
1                              aaaaa      2007-06-01
2                              aaaaa      2007-06-01
2007-06-02                                2007-06-02
3                              aaaaa      2007-06-02
4                              aaaaa      2007-06-02
2007-06-03                                2007-06-03
5                              aaaaa      2007-06-03
6                              aaaaa      2007-06-03
2007-06-04                                2007-06-04
7                              aaaaa      2007-06-04
8                              aaaaa      2007-06-04
9                              aaaaa      2007-06-04（所影响的行数为 13 行）
*/