如题:现有数据如下:
create table test
(
tid int identity primary key,
stdnum varchar(20),
kemuID int,
achieve int
)
go
insert into test
select '001',1,90 union all
select '001',2,76 union all
select '001',3,80 union all
select '002',1,77 union all
select '002',2,68 union all
select '002',3,64 union all
select '003',1,82 union all
select '003',2,74 union all
select '003',3,69
.
.
.
go
要得到结果:
stdnum sumachieve mingci
001 246 1
003 225 2
002 209 3
.
.
.
希望不用临时表可以得到以上数据,望高手指点下。
create table test
(
tid int identity primary key,
stdnum varchar(20),
kemuID int,
achieve int
)
go
insert into test
select '001',1,90 union all
select '001',2,76 union all
select '001',3,80 union all
select '002',1,77 union all
select '002',2,68 union all
select '002',3,64 union all
select '003',1,82 union all
select '003',2,74 union all
select '003',3,69
.
.
.
go
要得到结果:
stdnum sumachieve mingci
001 246 1
003 225 2
002 209 3
.
.
.
希望不用临时表可以得到以上数据,望高手指点下。
group by stdnum
*,
mingci=(
SELECT COUNT(*)+1
FROM(
SELECT stdnum,SUM(achieve) AS sumachieve
FROM test
GROUP BY stdnum
) AS A
WHERE sumachieve>T.sumachieve
)
FROM(
SELECT stdnum,SUM(achieve) AS sumachieve
FROM test
GROUP BY stdnum
) AS T
select *,ord=(select count(*)+1 from
(select stdnum,sum(achieve) as achieve from test
group by stdnum ) b where achieve>a.achieve
) from(
select stdnum,sum(achieve) as achieve from test
group by stdnum )a
)t order by ord
from
(select stdnum,sum(achieve)achieve from test group by stdnum)t
05用dense_rank/rand
go
create table test
(
tid int identity primary key,
stdnum varchar(20),
kemuID int,
achieve int
)
go
insert into test
select '001',1,90 union all
select '001',2,76 union all
select '001',3,80 union all
select '002',1,77 union all
select '002',2,68 union all
select '002',3,64 union all
select '003',1,82 union all
select '003',2,74 union all
select '003',3,69
select b.*,mingci=(select count(*)+1 from (select stdnum,sum(achieve )achieve from test group by stdnum) a where a.achieve>b. achieve) from
(select stdnum,sum(achieve )achieve from test group by stdnum)b order by mingci
/*stdnum achieve mingci
-------------------- ----------- -----------
001 246 1
003 225 2
002 209 3*/