如何在sql中排名前20％的顶级行 [英] how to get top rows in sql skipping top 20 percent

问题描述

大家好，

如何在第一个sql语句中获得前20％的行，然后在第二个sql语句中将前10％的行跳过前20％。



我尝试了第二个sql语句，但是如果使用order by子句则无法工作

Hi everyone,
How can I get top 20 percent rows in first sql statement and then next 10 percent rows skipping first 20 percent in second sql statement.

I tried this for second sql statement but not worked if used order by clause

select  top 10 percent from table1 where SerialNumber not in (select top 20 percent SerialNumber from table1 order by totalscore desc) order by totalscore desc

谢谢

Thanks

推荐答案

阅读本文： http://stackoverflow.com/questions/13220743/implement-paging-skip-take-functionality-with-this-query [< a href =http://stackoverflow.com/questions/13220743/implement-paging-skip-take-functionality-with-this-querytarget =_ blanktitle =New Window> ^ ]

SELECT * FROM
( SELECT 
    ROW_NUMBER() OVER (ORDER BY totalscore desc) AS ROW_NUMBER,rd.SerialNumber, pr.TotalScore,
    rd.Col_2_1_4 as FirstNameE, rd.Col_2_1_5 as MiddleNameE,rd.Col_2_1_6 as LastNameE,rd.Col_1_1 as District, rd.Col_1_2 as VDC,rd.Col_1_3 as WardNo,rd.Col_1_8_2_1 as Citizenship
  FROM PovertyResult pr inner join Records rd on pr.SerialNumber=rd.SerialNumber
  where 
	(((ISNULL(@District,'')='') or (rd.Col_1_8_2_3=@District)) and
	((ISNULL(@Vdc,'0')='0') or (rd.Col_1_2=@Vdc))
	and((ISNULL(@WardNo,'0')='0') or (rd.Col_1_3=@WardNo)))  
) AS Tbl
WHERE ROW_NUMBER > @First20Percent and ROW_NUMBER<= @10Percent

I solved my problem using above SQL Query

这篇关于如何在sql中排名前20％的顶级行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！