sql，统计_xing2516_新浪博客

关键字: sql，统计

数据表格如下：

我使用的是mysql数据库，因此相关的sql为

Sql代码​

下面的1、2、3是行号

1.CREATE TABLE `stuscore` (

2. `id` int(11) NOT NULL auto_increment,

3. `name` varchar(20) default NULL,

4. `subject` varchar(20) default NULL,

5. `score` varchar(20) default NULL,

 6. `stuid` varchar(10) default NULL,

 7. PRIMARY KEY (`id`)

8.) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 9.

10. 11.

12.insert into `stuscore`(`id`,`name`,`subject`,`score`,`stuid`) values 13.(1,'张三','数学','89','1'), 14.(2,'张三','语文','80','1'), 15.(3,'张三','英语','70','1'), 16.(4,'李四','数学','90','2'), 17.(5,'李四','语文','70','2'), 18.(6,'李四','英语','80','2'); CREATE TABLE `stuscore` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(20) default NULL,

`subject` varchar(20) default NULL,

`score` varchar(20) default NULL,

`stuid` varchar(10) default NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `stuscore`(`id`,`name`,`subject`,`score`,`stuid`) values

(1,'张三','数学','89','1'),

(2,'张三','语文','80','1'),

(3,'张三','英语','70','1'),

(4,'李四','数学','90','2'),

(5,'李四','语文','70','2'),

(6,'李四','英语','80','2');

以下是相关的问题：

1. 计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)

2. 计算每个人的总成绩并排名(要求显示字段: 学号，姓名，总成绩)

3. 计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)

4. 计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）

5. 列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)

6. 列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)

7. 统计如下：

5. 列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)

6. 列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)

8． 列出各门课程的平均成绩（要求显示字段：课程，平均成绩）

9． 列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）

10． 列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）

11． 求出李四的数学成绩的排名

13．统计如下：数学:张三(50分),李四(90分),王五(90分),赵六(76分)

答案如下：

1. 计算每个人的总成绩并排名（要求显示字段：姓名，总成绩）

Sql代码

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

select name,sum(score) as allscore from stuscore group by name order by allscore desc2. 计算每个人的总成绩并排名（要求显示字段: 学号，姓名，总成绩）

Sql代码

1.select stuid,name,sum(score) as allscore from stuscore group by name order by allscore desc

select stuid,name,sum(score) as allscore from stuscore group by name order by allscore desc3. 计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)

Sql代码

1.SELECT t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,

2.(SELECT stuid,max(score) as maxscore from stuscore group by stuid) t2 3.where t1.stuid=t2.stuid and t1.score=t2.maxscore SELECT t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,

(SELECT stuid,max(score) as maxscore from stuscore group by stuid) t2

where t1.stuid=t2.stuid and t1.score=t2.maxscore4. 计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）

Sql代码

1.select distinct t1.stuid,t1.name,t2.avgscore from stuscore t1,

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

(select stuid,avg(score) as avgscore from stuscore group by stuid) t2

where t1.stuid=t2.stuid5. 列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)

Sql代码

1.select t1.stuid,t1.name,t1.subject,t2.maxscore from stuscore t1,

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

(select subject,max(score) as maxscore from stuscore group by subject) t2

where t1.subject=t2.subject and t1.score=t2.maxscore6. 列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)

Sql代码

1.select distinct t1.* from stuscore t1 where t1.stuid in

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

(select top 2 stuscore.stuid from stuscore where subject = t1.subject order by score desc)

order by t1.subject该语句未测试，目前知道在mysql下无法运行。mysql不支持select top。

7.

Sql代码

1.select stuid as 学号,name as 姓名,

2.sum(case when subject='语文' then score else 0 end) as 语文, 3.sum(case when subject='数学' then score else 0 end) as 数学, 4.sum(case when subject='英语' then score else 0 end) as 英语, 5.sum(score) as 总分,(sum(score)/count(*)) as 平均分 6.from stuscore 7.group by stuid,name 8.order by 总分 desc 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 stuscore

group by stuid,name

order by 总分 desc8. 列出各门课程的平均成绩（要求显示字段：课程，平均成绩）

Sql代码

1.select subject,avg(score) as avgscore from stuscore group by subject

select subject,avg(score) as avgscore from stuscore group by subject9. 列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）

Sql代码

1.select * from stuscore where subject ='数学' order by score desc

select * from stuscore where subject ='数学' order by score desc 不知道我自己想法对不对？

10. 列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）

Sql代码

1.select t3.* from(

2.select top 2 t2.* from ( 3.select top 3 name,subject,score,stuid from stuscore where subject='数学' 4.order by score desc 5.) t2 order by t2.score 6.) t3 order by t3.score desc 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. 求出李四的数学成绩的排名

Sql代码

1.declare @tmp table(pm int,name varchar(50),score int,stuid int)

2.insert into @tmp select null,name,score,stuid from stuscore where subject='数学' order by score desc 3.declare @id int 4.set @id=0; 5.update @tmp set @id=@id+1,pm=@id 6.select * from @tmp where name='李四' 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 desc

declare @id int

set @id=0;

update @tmp set @id=@id+1,pm=@id

select * from @tmp where name='李四'12.

Sql代码

1.select subject,

2.(select count(*) from stuscore where score<60 and subject=t1.subject) as 不及格, 3.(select count(*) from stuscore where score between 60 and 80 and subject=t1.subject) as 良, 4.(select count(*) from stuscore where score >80 and subject=t1.subject) as 优 5.from stuscore t1 group by subject 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分)

Sql代码

1.declare @s varchar(1000)

2.set @s='' 3.select @s =@s+','+name+'('+convert(varchar(10),score)+'分)' from stuscore where subject='数学' 4.set @s=stuff(@s,1,1,'') 5.print '数学:'+@s