求一SQL语句.

Question

  id   aid    userid   good    bad
  7 11 3 1 0
  8 11 4 1 1
  12 8 3 1 0
  13 7 3 0 1
------------------------------------------------
select per=ltrim(cast(sum(good)*100.0/(sum(good)+sum(bad)) as numeric(5,0)))+'%',
per1=ltrim(cast(sum(bad)*100.0/(sum(good)+sum(bad)) as numeric(5,0)))+'%',aid
from aw_vote
group by aid
order by per asc
------------------------------------------------
per     per1   aid
0% 100% 7
100% 0% 8
67% 33% 11
------------------------------------------------
我想取出per的最高值的那一条记录.SQL语句该如何写??

Answer 1

--TRY
SELECT TOP 1 * FROM (
select per=ltrim(cast(sum(good)*100.0/(sum(good)+sum(bad)) as numeric(5,0)))+'%',
per1=ltrim(cast(sum(bad)*100.0/(sum(good)+sum(bad)) as numeric(5,0)))+'%',aid
from aw_vote
group by aid
)T ORDER BY per DESC

Answer 2

select top 1………………order by per desc

Answer 3

if object_id('[aw_vote]') is not null drop table [aw_vote]
go
create table [aw_vote]([id] int,[aid] int,[userid] int,[good] int,[bad] int)
insert [aw_vote]
select 7,11,3,1,0 union all
select 8,11,4,1,1 union all
select 12,8,3,1,0 union all
select 13,7,3,0,1select * from [aw_vote]
select top 1 per=ltrim(cast(sum(good)*100.0/(sum(good)+sum(bad)) as numeric(5,0)))+'%',
per1=ltrim(cast(sum(bad)*100.0/(sum(good)+sum(bad)) as numeric(5,0)))+'%',aid
from aw_vote
group by aid
order by sum(good)*1.0/(sum(good)+sum(bad)) desc
--测试结果:
/*
per                                        per1                                       aid
------------------------------------------ ------------------------------------------ -----------
100%                                       0%                                         8(1 行受影响)
*/