日期:2014-05-18  浏览次数:20653 次

请问如何按照时间排列并分类
表结构如下

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-1

2007-6-2
3 aaaaa 2007-6-2
4 aaaaa 2007-6-2

2007-6-3
5 aaaaa 2007-6-3
6 aaaaa 2007-6-3
....

以此类推。。

请指教。。。谢谢



------解决方案--------------------
SQL code
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

------解决方案--------------------
一天到晚游泳的鱼

又一个同名的哦。。。
------解决方案--------------------
SQL code
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') 
go

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

drop 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 行)
*/

------解决方案--------------------
SQL code
select top 1 
      b.titlecode 
from Table1 as b 
where b.dirname= '芙蓉姐姐的故事' 
      and exists (select *
                   from Table1  
                   where dirname= '芙蓉姐姐的故事' and titleid >b.titleid )