--类似,这个可能有问题 select top 5 max(a.a) a,a.b,b.c from a inner join b on a.a=b.a group by a.b,b.c order by 1 desc
create table t(a varchar(3),b varchar(2),c varchar(2))insert into t select 'No1','b1','c1' union all select 'No2','b1','c1' union all select 'No3','b1','c1' union all select 'No4','b1','c1' union all select 'No6','b1','c1' union all select 'No5','b1','c1' union all select 'No1','b1','c2' select a,b,c from t s where (select count(a)+1 from t where a>s.a and b=s.b and c=s.c)<=5 order by a desc
select b,c from tb where a in (select top 5 a from tb t where b=t.b c=t.c order by t.a desc )
select a,b,c,(select count(a)+1 from t where a>s.a and b=s.b and c=s.c) d from t s where (select count(a)+1 from t where a>s.a and b=s.b and c=s.c)<=5 order by a desc
你得看我的例子啊,你这样写不是只有5条记录吗,我的是每个b,c汇总以后都是5条,所以如果根据b,c汇总有10条记录,那么最终我要得到的记录的话最多有50条,最少也应该有10条,明白?????
--类似,这个可能有问题
select top 5 max(a.a) a,a.b,b.c from a inner join b on a.a=b.a group by a.b,b.c order by 1 desc
create table t(a varchar(3),b varchar(2),c varchar(2))insert into t
select 'No1','b1','c1'
union all select 'No2','b1','c1'
union all select 'No3','b1','c1'
union all select 'No4','b1','c1'
union all select 'No6','b1','c1'
union all select 'No5','b1','c1'
union all select 'No1','b1','c2' select a,b,c
from t s where (select count(a)+1 from t where a>s.a and b=s.b and c=s.c)<=5 order by a desc
from tb
where a in (select top 5 a from tb t where b=t.b c=t.c order by t.a desc )
在此基础上,我加一临时列得到:
No1 b1 c1
No2 b1 c1
No3 b1 c1
No4 b1 c1
No5 b1 c1
No6 b1 c1
No1 b1 c2
...
得到:
1 No6 b1 c1
2 No5 b1 c1
3 No4 b1 c1
4 No3 b1 c1
5 No2 b1 c1
1 No1 b1 c2
即b、c汇总以后前面是1,2,3,4,5编号的???
谢谢了。
from t s where (select count(a)+1 from t where a>s.a and b=s.b and c=s.c)<=5 order by a desc