1. excel中sql如何找出考試成績總分前5名的同學
可以用row_number函數來解決。
1、創建測試表，插入數據：
createtablesc(idint,namevarchar(20),classvarchar(20),scoreint);insertintoscvalues(1,'badkano','一年一班',100)insertintoscvalues(2,'網路知道團長','一年一班',99)insertintoscvalues(3,'小短','一年一班',95)insertintoscvalues(4,'小小動','一年一班',97)insertintoscvalues(5,'小智','一年一班',80)insertintoscvalues(6,'呂布','一年二班',67)insertintoscvalues(7,'趙雲','一年二班',90)insertintoscvalues(8,'典韋','一年二班',89)insertintoscvalues(9,'關羽','一年二班',70)insertintoscvalues(10,'馬超','一年二班',98)
2、查詢每個班級的前五名，可用語句：
select*from(selectrow_number()over()排名,*fromsc)twhere排名<=3orderbyclassasc,scoredesc
3、結果截圖：
2. SQL查詢語句： 查詢 每科目分數最高的5項(學生姓名，科目，分數)
sql server 2005及以上版本如下，sql server 2000的話得用別的方法了
declare @t table (
sname varchar(30)
,ssubject varchar(30)
,score int
)
insert into @t
select '姓名1' as n,'科目1' as s,'95' as sc union all
select '姓名2' as n,'科目1' as s,'83' as sc union all
select '姓名3' as n,'科目1' as s,'90' as sc union all
select '姓名4' as n,'科目1' as s,'75' as sc union all
select '姓名7' as n,'科目1' as s,'71' as sc union all
select '姓名8' as n,'科目1' as s,'95' as sc union all
select '姓名9' as n,'科目1' as s,'86' as sc union all
select '姓名10' as n,'科目1' as s,'73' as sc union all
select '姓名11' as n,'科目1' as s,'76' as sc union all
select '姓名13' as n,'科目1' as s,'96' as sc union all
select '姓名14' as n,'科目1' as s,'73' as sc union all
select '姓名15' as n,'科目1' as s,'77' as sc union all
select '姓名6' as n,'科目2' as s,'88' as sc union all
select '姓名7' as n,'科目2' as s,'64' as sc union all
select '姓名8' as n,'科目2' as s,'91' as sc union all
select '姓名9' as n,'科目2' as s,'66' as sc union all
select '姓名12' as n,'科目2' as s,'69' as sc union all
select '姓名13' as n,'科目2' as s,'93' as sc union all
select '姓名14' as n,'科目2' as s,'90' as sc union all
select '姓名15' as n,'科目2' as s,'67' as sc union all
select '姓名18' as n,'科目2' as s,'65' as sc union all
select '姓名19' as n,'科目2' as s,'78' as sc union all
select '姓名20' as n,'科目2' as s,'88' as sc union all
select '姓名21' as n,'科目2' as s,'96' as sc union all
select '姓名1' as n,'科目3' as s,'77' as sc union all
select '姓名2' as n,'科目3' as s,'79' as sc union all
select '姓名4' as n,'科目3' as s,'84' as sc union all
select '姓名5' as n,'科目3' as s,'71' as sc union all
select '姓名9' as n,'科目3' as s,'76' as sc union all
select '姓名10' as n,'科目3' as s,'61' as sc union all
select '姓名11' as n,'科目3' as s,'63' as sc union all
select '姓名12' as n,'科目3' as s,'77' as sc union all
select '姓名13' as n,'科目3' as s,'69' as sc union all
select '姓名14' as n,'科目3' as s,'89' as sc union all
select '姓名19' as n,'科目3' as s,'94' as sc union all
select '姓名20' as n,'科目3' as s,'92' as sc union all
select '姓名21' as n,'科目3' as s,'82' as sc union all
select '姓名22' as n,'科目3' as s,'65' as sc union all
select '姓名23' as n,'科目3' as s,'63' as sc union all
select '姓名26' as n,'科目3' as s,'83' as sc
--這是查詢語句，把@t改成自己實際表名，欄位改成實際欄位名
select sname
,ssubject
,score from (
SELECT sname
,ssubject
,score
,row_number() over (PARTITION by ssubject order by score desc) as gorder
FROM @t
) as a
where gorder < 6
3. Access SQL語言怎麼選擇每個班級的前五名分數
在access中不好做，你將數據導出到Excel中，然後插入數據透視表，就會輕松搞定。
4. SQL如何處理分數排名
SQL如何處理分數排名
SELECT top 5
FROM 表名
ORDER BY 欄位名 DESC
5. 用SQL列出資料庫成績的前五名學生的學號、姓名、成績
一樓的：
select student.學號,姓名,成績 FROM STUDENT,SCORE WHERE STUDENT.學號=SCORE.學號 ORDER BY 成績 DESC TOP 5 (本語句適合二級) 追問(急！！！)找出各科成績均在85分以上（含）的學生的學號、姓名 這個呢？ 回答完就給分^-^
回答追問的：
select student.學號,姓名,min(成績) 最低分,max(成績) 最高分 FROM STUDENT,SCORE
WHERE STUDENT.學號=SCORE.學號
group by student.學號,姓名
having min(成績)>=85
6. sql取出每個分組的前幾條
一個簡單的方法就是排序之後給它分配序號，根據序號去找前幾條
select*
from(select公司,人數,
--按人數從多到少分配序號，不同公司會重1開始
rank()over(partitionby公司orderby人數desc)asxh
from公司檔案)asA
wherexh<=2--取前幾位就寫幾
orderby公司,人數desc
排序效果如下
7. sql server 2005 查找並統計前5名數量最多的類型
SELECT top 5 aType,count(*) as numbers FROM Articles
where aType in(6,7,8,9,10)
group by aType
order by numbers DESC
8. 取每個班前5名成績的sql怎麼寫
--單純通過top是不行的，要用到row_number()over()才能取到每個班的分別的前5名
select*from(selectt.*，row_number()over(partitionby班級orderby成績desc)asfnumfrom表名T)t1wherefnum<=5
9. sql 語句查詢 前5名後5名的成績
不知道你的是什麼資料庫，我用oracle的寫法了。
兩種辦法：
分別求最大和最小，然後union all
select * from（select* from table order by 成績） where rownum<=5
union all
select * from（select* from table order by 成績 desc） where rownum<=5
利用排序，找到每個人的位置，然後輸出。排序的方法很多，可以用rownum排序，也可以用row_number()over()排序
我用row_number()over()寫一個
selecta.姓名,a.成績 from
(select row_number()over(order by 成績) num,姓名,成績 from table) a where a.num<=5 or
a.num>=(select count(*)-5 from table)
我沒實驗，不過就算有問題也應該不大。
sqlserver的版本是啥？是2005以上么？如果是
那麼利用那個row_number的應該也可以，不過為了讓分數一樣的人都出來，那麼最好改為
這種情況是假設前五齣現分數相同的話，假如前五名有六個人的情況，不過我沒有輸出名次，另外我上面的那種寫法也可以試試，sqlserver好像也可以。
不過如果你要是sql2005以前的版本那時sqlserver還沒有這幾個開窗函數，那就稍微有點麻煩了。
select姓名,成績 from
select rank()over(order by 成績) num,rank()over(order by 成績 desc) num_desc,姓名,成績 from table) a where a.num<=5 or a.num_desc<=5 order by 成績 desc
10. SQL語句：十個班裡各取前五名
--創建測試數據
declare @t table(ID int,xm varchar(20),total int,banji varchar(50))
insert @t
select 1,'張一',100,'A班' union all
select 2,'張二',200,'A班' union all
select 3,'張三',300,'A班' union all
select 4,'張四',400,'A班' union all
select 5,'張五',500,'B班' union all
select 6,'張六',600,'B班' union all
select 7,'張七',700,'B班' union all
select 8,'張八',800,'B班' union all
select 9,'張九',900,'C班' union all
select 10,'張十',1000,'C班' union all
select 11,'張十一',1100,'C班' union all
select 12,'張十二',1200,'C班'
--查詢
select * from @t a
where (select count(*) from @t b where b.banji = a.banji and b.total > a.total) < 3
--這里是前三名，前五名只需把這里的<3改為<5即可
--結果
/*
ID xm total banji
---------------------------------------------
2 張二 200 A班
3 張三 300 A班
4 張四 400 A班
6 張六 600 B班
7 張七 700 B班
8 張八 800 B班
10 張十 1000 C班
11 張十一 1100 C班
12 張十二 1200 C班
*/