SQL Server 统计sql语句大全收藏
2022-11-12 09:53:38
内容摘要
这篇文章主要为大家详细介绍了SQL Server 统计sql语句大全收藏，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！1.计算每个人的总成绩并排
文章正文
这篇文章主要为大家详细介绍了SQL Server 统计sql语句大全收藏，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！
1.计算每个人的总成绩并排名select name,sum(score) as allscore from stuscore group by name order by allscore2.计算每个人的总成绩并排名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 desc3. 计算每个人单科的最高成绩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.maxscore4.计算每个人的平均成绩 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.stuid5.列出各门课程成绩最好的学生 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.maxscore6.列出各门课程成绩最好的两位学生 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.subject7.学号 姓名 语文 数学 英语 总分 平均分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 总分desc8.列出各门课程的平均成绩select subject,avg(score) as avgscore from stuscoregroup by subject9.列出数学成绩的排名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 @tmpselect DENSE_RANK () OVER(order by score desc) as row,name,subject,score,stuid from stuscore where subject='数学'order by score desc10. 列出数学成绩在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 desc11. 求出李四的数学成绩的排名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 subject13. 数学:张三(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注：关于SQL Server 统计sql语句大全收藏的内容就先介绍到这里，更多相关文章的可以留意
代码注释
作者：喵哥笔记
