主頁 > 知識庫 > sqlserver 統(tǒng)計sql語句大全收藏

sqlserver 統(tǒng)計sql語句大全收藏

熱門標(biāo)簽:貴州房產(chǎn)智能外呼系統(tǒng)供應(yīng)商 電銷外呼有錄音系統(tǒng)有哪些 申請400電話在哪辦理流程 臨沂智能電銷機(jī)器人加盟哪家好 鎮(zhèn)江網(wǎng)路外呼系統(tǒng)供應(yīng)商 百度地圖標(biāo)注改顏色 小e電話機(jī)器人 一個導(dǎo)航軟件能用幾個地圖標(biāo)注點 外呼運營商線路收費
1.計算每個人的總成績并排名

select name,sum(score) as allscore from stuscore group by name order by allscore

2.計算每個人的總成績并排名

select distinct t1.name,t1.stuid,t2.allscore from stuscore t1,( select stuid,sum(score) as allscore from stuscore group by stuid)t2where t1.stuid=t2.stuidorder by t2.allscore desc

3. 計算每個人單科的最高成績

select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,(select stuid,max(score) as maxscore from stuscore group by stuid) t2where t1.stuid=t2.stuid and t1.score=t2.maxscore

4.計算每個人的平均成績

select distinct t1.stuid,t1.name,t2.avgscore from stuscore t1,(select stuid,avg(score) as avgscore from stuscore group by stuid) t2where t1.stuid=t2.stuid

5.列出各門課程成績最好的學(xué)生

select t1.stuid,t1.name,t1.subject,t2.maxscore from stuscore t1,(select subject,max(score) as maxscore from stuscore group by subject) t2where t1.subject=t2.subject and t1.score=t2.maxscore
6.列出各門課程成績最好的兩位學(xué)生

select distinct t1.* from stuscore t1 where t1.id in (select top 2 stuscore.id from stuscore where subject = t1.subject order by score desc) order by t1.subject

7.學(xué)號 姓名 語文 數(shù)學(xué) 英語 總分 平均分

select stuid as 學(xué)號,name as 姓名,sum(case when subject='語文' then score else 0 end) as 語文,sum(case when subject='數(shù)學(xué)' then score else 0 end) as 數(shù)學(xué),sum(case when subject='英語' then score else 0 end) as 英語,sum(score) as 總分,(sum(score)/count(*)) as 平均分from stuscoregroup by stuid,name order by 總分desc

8.列出各門課程的平均成績

select subject,avg(score) as avgscore from stuscoregroup by subject

9.列出數(shù)學(xué)成績的排名

declare @tmp table(pm int,name varchar(50),score int,stuid int)insert into @tmp select null,name,score,stuid from stuscore where subject='數(shù)學(xué)' order by score descdeclare @id intset @id=0;update @tmp set @id=@id+1,pm=@idselect * from @tmp

select DENSE_RANK () OVER(order by score desc) as row,name,subject,score,stuid from stuscore where subject='數(shù)學(xué)'order by score desc

10. 列出數(shù)學(xué)成績在2-3名的學(xué)生

select t3.* from(select top 2 t2.* from (select top 3 name,subject,score,stuid from stuscore where subject='數(shù)學(xué)'order by score desc) t2 order by t2.score) t3 order by t3.score desc

11. 求出李四的數(shù)學(xué)成績的排名

declare @tmp table(pm int,name varchar(50),score int,stuid int)insert into @tmp select null,name,score,stuid from stuscore where subject='數(shù)學(xué)' order by score descdeclare @id intset @id=0;update @tmp set @id=@id+1,pm=@idselect * from @tmp where name='李四'

12. 課程 不及格(-59) 良(-80) 優(yōu)(-100)

select subject, (select count(*) from stuscore where score60 and subject=t1.subject) as 不及格,(select count(*) from stuscore where score between 60 and 80 and subject=t1.subject) as 良,(select count(*) from stuscore where score >80 and subject=t1.subject) as 優(yōu)from stuscore t1 group by subject

13. 數(shù)學(xué):張三(50分),李四(90分),王五(90分),趙六(76分)

declare @s varchar(1000)set @s=''select @s =@s+','+name+'('+convert(varchar(10),score)+'分)' from stuscore where subject='數(shù)學(xué)' set @s=stuff(@s,1,1,'')print '數(shù)學(xué):'+@s
您可能感興趣的文章:
  • 數(shù)據(jù)庫SQL語句優(yōu)化總結(jié)(收藏)
  • 收藏的SQL知識以及SQL語句簡單實踐通俗易懂
  • 一些有用的sql語句整理 推薦收藏
  • 程序員最實用的 SQL 語句收藏,看完這篇就夠了

標(biāo)簽:日照 延邊 三明 晉城 保定 嘉興 澳門 合肥

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《sqlserver 統(tǒng)計sql語句大全收藏》,本文關(guān)鍵詞  sqlserver,統(tǒng)計,sql,語句,大全,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《sqlserver 統(tǒng)計sql語句大全收藏》相關(guān)的同類信息!
  • 本頁收集關(guān)于sqlserver 統(tǒng)計sql語句大全收藏的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章