没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
sqlserver 统计统计sql语句大全收藏语句大全收藏
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
资源评论
weixin_38672731
- 粉丝: 5
- 资源: 952
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功