select * from 表 order by [time] desc,[name] desc,[id] asc
declare @a table(a int,b varchar(10),c datetime) insert @a select 1,'Tom','2004-11-05 11:00:00' union all select 2,'Mary','2004-11-05 11:58:00' union all select 3,'Tom','2004-11-05 11:00:00' union all select 4,'Mary','2004-11-05 10:55:00' union all select 5,'Tom','2004-11-05 11:05:00' union all select 6,'Tom',' 2004-11-05 10:00:00' union all select 7,'jacq','2004-11-05 10:05:00' union all select 8,'jacq','2004-11-05 10:58:00' union all select 9,'jacq','2004-11-05 11:00:00' union all select 10,'jacq','2004-11-05 11:05:00' union all select 11,'Tom',' 2004-11-05 10:05:00'select * from @a A order by c, (case when (select top 1 b from @a where c<a.c order by c desc)=b then 0 else 1 end),ba b c ----------- ---------- ------------------------ 6 Tom 2004-11-05 10:00:00.000 11 Tom 2004-11-05 10:05:00.000 7 jacq 2004-11-05 10:05:00.000 4 Mary 2004-11-05 10:55:00.000 8 jacq 2004-11-05 10:58:00.000 9 jacq 2004-11-05 11:00:00.000 1 Tom 2004-11-05 11:00:00.000 3 Tom 2004-11-05 11:00:00.000 5 Tom 2004-11-05 11:05:00.000 10 jacq 2004-11-05 11:05:00.000 2 Mary 2004-11-05 11:58:00.000(所影响的行数为 11 行)
insert @a select 1,'Tom','2004-11-05 11:00:00'
union all select 2,'Mary','2004-11-05 11:58:00'
union all select 3,'Tom','2004-11-05 11:00:00'
union all select 4,'Mary','2004-11-05 10:55:00'
union all select 5,'Tom','2004-11-05 11:05:00'
union all select 6,'Tom',' 2004-11-05 10:00:00'
union all select 7,'jacq','2004-11-05 10:05:00'
union all select 8,'jacq','2004-11-05 10:58:00'
union all select 9,'jacq','2004-11-05 11:00:00'
union all select 10,'jacq','2004-11-05 11:05:00'
union all select 11,'Tom',' 2004-11-05 10:05:00'select * from @a A order by c,
(case when (select top 1 b from @a where c<a.c order by c desc)=b then 0 else 1 end),ba b c
----------- ---------- ------------------------
6 Tom 2004-11-05 10:00:00.000
11 Tom 2004-11-05 10:05:00.000
7 jacq 2004-11-05 10:05:00.000
4 Mary 2004-11-05 10:55:00.000
8 jacq 2004-11-05 10:58:00.000
9 jacq 2004-11-05 11:00:00.000
1 Tom 2004-11-05 11:00:00.000
3 Tom 2004-11-05 11:00:00.000
5 Tom 2004-11-05 11:05:00.000
10 jacq 2004-11-05 11:05:00.000
2 Mary 2004-11-05 11:58:00.000(所影响的行数为 11 行)