刚才问了一个问题: 有两个字段 number(int) ID(string) 2 1021
3 5026
9 9031
7 6032
2 4035 我希望能将ID按照中间两位分组 ,这个实例中就是前2项一组(*02*),后3项一组(*03*)
同时对每组的number进行sum 有朋友回答我 用这样的语句实现: SELECT sum(number),substring(ID,2,2) from Table group by(substring(ID,2,2)) order by sum(number) 成功解决,返回结果 numbe ID
5 02
18 03 现在的问题是:如果我想再加上一列排名序号 numbe ID rank
5 02 1
18 03 2 不知道是否仍然能够在一条语句中完成?我看到有关加排名列的帖子都是直接排名的,没有先group。 谢谢!
然后再删除建立的表? 是这样吗? 初学乍到,见笑了 -_-|
insert @t
select 2,'1021' union all
select 3,'5026' union all
select 9,'9031' union all
select 7,'6032' union all
select 2,'4035'----不使用临时表
select *,rank =
(select count(*) from (select sum(number) as number from @t group by substring(id,2,2)) x where x.number <= a.number)
from
(
select sum(number) as number,substring(id,2,2) as id from @t group by substring(id,2,2)
) a order by rank----使用临时表
select sum(number) as number,substring(id,2,2) as id,rank = identity(int,1,1) into #tmp from @t group by substring(id,2,2) order by 1
select * from #tmpdrop table #tmp