sqlserver 統計sql語句年夜全珍藏。本站提示廣大學習愛好者：（sqlserver 統計sql語句年夜全珍藏）文章只能為提供參考，不一定能成為您想要的結果。以下是sqlserver 統計sql語句年夜全珍藏正文
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.列出各門課程成就最好的先生
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.列出各門課程成就最好的兩位先生
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.學號 姓名 語文 數學 英語 總分 均勻分
select stuid as 學號,name as 姓名,sum(case when subject='語文' then score else 0 end) as 語文,sum(case when subject='數學' then score else 0 end) as 數學,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.列出數學成就的排名
declare @tmp table(pm int,name varchar(50),score int,stuid int)insert into @tmp select null,name,score,stuid from stuscore where subject='數學' 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='數學'order by score desc
10. 列出數學成就在2-3名的先生
select t3.* from(select top 2 t2.* from (select top 3 name,subject,score,stuid from stuscore where subject='數學'order by score desc) t2 order by t2.score) t3 order by t3.score desc
11. 求出李四的數學成就的排名
declare @tmp table(pm int,name varchar(50),score int,stuid int)insert into @tmp select null,name,score,stuid from stuscore where subject='數學' order by score descdeclare @id intset @id=0;update @tmp set @id=@id+1,pm=@idselect * from @tmp where name='李四'
12. 課程 不合格（-59） 良（-80） 優（-100）
select subject, (select count(*) from stuscore where score<60 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 優from stuscore t1 group by subject
13. 數學:張三(50分),李四(90分),王五(90分),趙六(76分)
declare @s varchar(1000)set @s=''select @s =@s+','+name+'('+convert(varchar(10),score)+'分)' from stuscore where subject='數學' set @s=stuff(@s,1,1,'')print '數學:'+@s
