[id] [group] [time]
1 a xx
2 b xx
3 b xx
4 a xx
5 a xx
6 c xx
7 a xx
时间部分省略了要求。按时间倒序,每一组取出一条数据,一轮一轮排下去,就像[id] [group] [time]
1 a xx
2 b xx
6 c xx
4 a xx
3 b xx
5 a xx
7 a xx结果是a,b,c 3组各取一条放在一齐,当然,a,b,c有时候是没有数据或数据量不相等的
1 a xx
2 b xx
3 b xx
4 a xx
5 a xx
6 c xx
7 a xx
时间部分省略了要求。按时间倒序,每一组取出一条数据,一轮一轮排下去,就像[id] [group] [time]
1 a xx
2 b xx
6 c xx
4 a xx
3 b xx
5 a xx
7 a xx结果是a,b,c 3组各取一条放在一齐,当然,a,b,c有时候是没有数据或数据量不相等的
insert into @tb select 1,'a','xx'
insert into @tb select 2,'b','xx'
insert into @tb select 3,'b','xx'
insert into @tb select 4,'a','xx'
insert into @tb select 5,'a','xx'
insert into @tb select 6,'c','xx'
insert into @tb select 7,'a','xx'select *,px=(select count(*) from @tb where b=t.b and a<=t.a)
from @tb t
order by px,ba b c px
1 a xx 1
2 b xx 1
6 c xx 1
4 a xx 2
3 b xx 2
5 a xx 3
7 a xx 4
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T ([id] int,[group] varchar(1),[time] datetime)
insert into #T
select 1,'a',getdate()+1 union all
select 2,'a',getdate()+2 union all
select 3,'a',getdate()+3 union all
select 4,'a',getdate()+4 union all
select 5,'b',getdate()+1 union all
select 6,'b',getdate()+2 union all
select 7,'c',getdate()+3select * from #T as t where [time] = (select max([time]) from #T where [group] = t.[group]) order by 1
/*
id group time
----------- ----- -----------------------
4 a 2008-04-16 16:04:04.250
6 b 2008-04-14 16:04:04.250
7 c 2008-04-15 16:04:04.250
*/
insert into @tb select 1,'a','xx'
insert into @tb select 2,'b','xx'
insert into @tb select 3,'b','xx'
insert into @tb select 4,'a','xx'
insert into @tb select 5,'a','xx'
insert into @tb select 6,'c','xx'
insert into @tb select 7,'a','xx'
select a,b,c from (
select *,px=(select count(*) from @tb where b=t.b and a<=t.a)
from @tb t)tp
order by px,ba b c
1 a xx
2 b xx
6 c xx
4 a xx
3 b xx
5 a xx
7 a xx不是和结果一个样吗
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T ([id] int,[group] varchar(1),[time] datetime)
insert into #T
select 1,'a',getdate()+1 union all
select 2,'a',getdate()+2 union all
select 3,'a',getdate()+3 union all
select 4,'a',getdate()+4 union all
select 5,'b',getdate()+1 union all
select 6,'b',getdate()+2 union all
select 7,'c',getdate()+3select * from #T order by 2,3 desc
/*
id group time
----------- ----- -----------------------
4 a 2008-04-16 16:06:17.123 <--
3 a 2008-04-15 16:06:17.123 <--
2 a 2008-04-14 16:06:17.123
1 a 2008-04-13 16:06:17.123
6 b 2008-04-14 16:06:17.123 <--
5 b 2008-04-13 16:06:17.123 <--
7 c 2008-04-15 16:06:17.123 <--
*/select * from #T as t where [time] in (select top 2 time from #T where [group] = t.[group] order by time desc) order by 2, 3 desc
/*
id group time
----------- ----- -----------------------
4 a 2008-04-16 16:06:17.123
3 a 2008-04-15 16:06:17.123
6 b 2008-04-14 16:06:17.123
5 b 2008-04-13 16:06:17.123
7 c 2008-04-15 16:06:17.123
*/
select *,px=(select count(*) from @tb where b=t.b and c<=t.c)
from @tb t)tp
order by px,c desc,b在wzy_love_sly基础上改成这样就正确了
declare @tb table ([ID] int,[GROUP] varchar(10),[TIME] varchar(10))
insert into @tb select 1,'a','xx'
insert into @tb select 2,'b','xx'
insert into @tb select 3,'b','xx'
insert into @tb select 4,'a','xx'
insert into @tb select 5,'a','xx'
insert into @tb select 6,'c','xx'
insert into @tb select 7,'a','xx'SELECT [ID],[GROUP],[TIME] FROM (SELECT *,PX=(SELECT COUNT(*) FROM @TB WHERE [GROUP]=A.[GROUP] AND [ID]<=A.[ID] ) FROM @TB A) T
ORDER BY PX,[GROUP]RESULT:
[ID],[GROUP],[TIME]
1 a xx
2 b xx
6 c xx
4 a xx
3 b xx
5 a xx
7 a xx
wzy_love_sly 正解declare @tb table (a int,b varchar(10),c varchar(10))
insert into @tb select 1,'a','xx'
insert into @tb select 2,'b','xx'
insert into @tb select 3,'b','xx'
insert into @tb select 4,'a','xx'
insert into @tb select 5,'a','xx'
insert into @tb select 6,'c','xx'
insert into @tb select 7,'a','xx'select *
from @tb t
order by (select count(1) from @tb where b=t.b and a<=t.a) ,b
insert into @tb select 1,'a','2001-01-01'
insert into @tb select 2,'b','2001-01-02'
insert into @tb select 3,'b','2001-01-03'
insert into @tb select 4,'a','2001-01-04'
insert into @tb select 5,'a','2001-01-06'
insert into @tb select 6,'c','2001-01-07'
insert into @tb select 7,'a','2001-01-08'select *
from @tb t
order by (select count(1) from @tb where b=t.b and c>=t.c) ,b,c
/*
a b c
----------- ---------- ------------------------------------------------------
7 a 2001-01-08 00:00:00.000
3 b 2001-01-03 00:00:00.000
6 c 2001-01-07 00:00:00.000
5 a 2001-01-06 00:00:00.000
2 b 2001-01-02 00:00:00.000
4 a 2001-01-04 00:00:00.000
1 a 2001-01-01 00:00:00.000(所影响的行数为 7 行)
*/
declare @tb table (a int,b varchar(10),c varchar(10))
insert into @tb select 1,'a','xx'
insert into @tb select 2,'b','xx'
insert into @tb select 3,'b','xx'
insert into @tb select 4,'a','xx'
insert into @tb select 5,'a','xx'
insert into @tb select 6,'c','xx'
insert into @tb select 7,'a','xx'select *,px=(select count(*) from @tb where b=t.b and a<=t.a)
from @tb t
order by px,b