27,579
社区成员
发帖
与我相关
我的任务
分享
create view GradeAVG
as
select substring(班级,1,charindex('年级',班级)+1),avg(语文),avg(数学),avg(英语) from tb group by substring(班级,1,charindex('年级',班级)+1)
go
create view ClassAVG
as
select 班级,avg(语文),avg(数学),avg(英语) from tb group by 班级
go
三:各班成绩总分前三名
with
wang as (select 排名=row_number() over (partition by 班级 order by 语文+数学+英语 ), 班级,姓名,总分=(语文+数学+英语) from tb)
select * from wang where 排名<=3
四:各年级总分前三名
with
wang as (select 排名=row_number() over (partition by left(班级,3) order by 语文+数学+英语 ), 班级,姓名,总分=(语文+数学+英语) from tb)
select * from wang where 排名<=3
create view Avg_Grade
as
select
年级=left(班级,3),
语文平均分=avg(语文),
数学平均分=avg(数学),
英语平均分=avg(英语),
总平均分=avg(语文+数学+英语)/3
from
tb
group by
left(班级,3)
GO
create view Avg_Class
as
select
班级,
语文平均分=avg(语文),
数学平均分=avg(数学),
英语平均分=avg(英语),
总平均分=avg(语文+数学+英语)/3
from
tb
group by
班级
select * from Avg_Grade
/**
年级 语文平均分 数学平均分 英语平均分 总平均分
------ ----------- ----------- ----------- -----------
二年级 80 80 80 80
六年级 84 73 79 79
三年级 77 82 79 79
四年级 90 76 76 80
五年级 84 81 77 81
一年级 85 80 72 79
(所影响的行数为 6 行)
**/
select * from Avg_Class
/**
班级 语文平均分 数学平均分 英语平均分 总平均分
---------- ----------- ----------- ----------- -----------
二年级1班 80 80 80 80
六年级1班 81 81 78 80
六年级2班 88 61 82 77
三年级1班 77 84 84 82
三年级2班 77 78 72 76
四年级1班 88 70 74 77
四年级2班 92 81 78 84
五年级1班 84 81 77 81
一年级1班 88 83 72 81
一年级2班 83 78 72 78
(所影响的行数为 10 行)
**/
declare @t table(id int,班级 varchar(10),姓名 varchar(10), 语文 int, 数学 int, 英语 int )
insert @t select 1,'一年级1班','张同学',78,78,78
insert @t select 2,'一年级1班','李同学',98,89,67
insert @t select 3,'一年级2班','王同学',78,67,78
insert @t select 4,'一年级2班','大同学',89,89,67
insert @t select 5,'二年级1班','要同学',67,78,89
insert @t select 6,'二年级1班','红同学',98,67,67
insert @t select 7,'二年级1班','郑同学',80,87,78
insert @t select 8,'二年级1班','刘同学',78,89,89
insert @t select 9,'三年级2班','缪同学',67,67,67
insert @t select 10,'三年级2班','付同学',88,89,78
insert @t select 11,'三年级1班','黄同学',78,77,87
insert @t select 12,'三年级1班','郭同学',78,99,89
insert @t select 13,'三年级1班','女同学',76,78,78
insert @t select 14,'四年级1班','男同学',86,67,67
insert @t select 15,'四年级1班','小同学',90,67,78
insert @t select 16,'四年级2班','大同学',89,78,90
insert @t select 17,'四年级2班','易同学',98,89,89
insert @t select 18,'四年级1班','付同学',90,78,78
insert @t select 19,'四年级2班','李同学',89,78,56
insert @t select 20,'五年级1班','王同学',98,98,67
insert @t select 21,'五年级1班','大同学',88,78,89
insert @t select 22,'五年级1班','要同学',96,67,89
insert @t select 23,'五年级1班','红同学',89,89,87
insert @t select 24,'五年级1班','郑同学',59,67,67
insert @t select 25,'五年级1班','刘同学',78,89,67
insert @t select 26,'六年级1班','缪同学',67,78,78
insert @t select 27,'六年级1班','付同学',89,89,78
insert @t select 28,'六年级2班','黄同学',87,67,76
insert @t select 29,'六年级2班','郭同学',90,56,89
insert @t select 30,'六年级1班','女同学',88,78,78
select left(班级,3) 年级, avg(语文) 语文,avg(数学) 数学,avg(英语) 英语
from @t
group by left(班级,3)
/*
年级 语文 数学 英语
------ ----------- ----------- -----------
二年级 80 80 80
六年级 84 73 79
三年级 77 82 79
四年级 90 76 76
五年级 84 81 77
一年级 85 80 72
(6 行受影响)
*/
select 班级, avg(语文) 语文,avg(数学) 数学,avg(英语) 英语
from @t
group by 班级
/*
班级 语文 数学 英语
---------- ----------- ----------- -----------
二年级1班 80 80 80
六年级1班 81 81 78
六年级2班 88 61 82
三年级1班 77 84 84
三年级2班 77 78 72
四年级1班 88 70 74
四年级2班 92 81 78
五年级1班 84 81 77
一年级1班 88 83 72
一年级2班 83 78 72
(10 行受影响)
*/
或者要这样.select 班级,avg(语文),avg(数学),avg(英语)
from tableName
group by 班级
select 年级,avg(语文),avg(数学),avg(英语)
from tableName
group by 年级
CREATE VIEW vw_TableNameGrade --创建视图
as
select 班级,avg(语文),avg(数学),avg(英语)
from tableName
group by 班级
GO
CREATE VIEW vw_TableNameClass --创建视图
as
select 年级,avg(语文),avg(数学),avg(英语)
from tableName
group by 年级
declare @t table(id int,班级 varchar(10),姓名 varchar(10), 语文 int, 数学 int, 英语 int )
insert @t select 1,'一年级1班','张同学',78,78,78
insert @t select 2,'一年级1班','李同学',98,89,67
insert @t select 3,'一年级2班','王同学',78,67,78
insert @t select 4,'一年级2班','大同学',89,89,67
insert @t select 5,'二年级1班','要同学',67,78,89
insert @t select 6,'二年级1班','红同学',98,67,67
insert @t select 7,'二年级1班','郑同学',80,87,78
insert @t select 8,'二年级1班','刘同学',78,89,89
insert @t select 9,'三年级2班','缪同学',67,67,67
insert @t select 10,'三年级2班','付同学',88,89,78
insert @t select 11,'三年级1班','黄同学',78,77,87
insert @t select 12,'三年级1班','郭同学',78,99,89
insert @t select 13,'三年级1班','女同学',76,78,78
insert @t select 14,'四年级1班','男同学',86,67,67
insert @t select 15,'四年级1班','小同学',90,67,78
insert @t select 16,'四年级2班','大同学',89,78,90
insert @t select 17,'四年级2班','易同学',98,89,89
insert @t select 18,'四年级1班','付同学',90,78,78
insert @t select 19,'四年级2班','李同学',89,78,56
insert @t select 20,'五年级1班','王同学',98,98,67
insert @t select 21,'五年级1班','大同学',88,78,89
insert @t select 22,'五年级1班','要同学',96,67,89
insert @t select 23,'五年级1班','红同学',89,89,87
insert @t select 24,'五年级1班','郑同学',59,67,67
insert @t select 25,'五年级1班','刘同学',78,89,67
insert @t select 26,'六年级1班','缪同学',67,78,78
insert @t select 27,'六年级1班','付同学',89,89,78
insert @t select 28,'六年级2班','黄同学',87,67,76
insert @t select 29,'六年级2班','郭同学',90,56,89
insert @t select 30,'六年级1班','女同学',88,78,78
select left(班级,3) 年级, avg(成绩) 各科平均成绩
from
(select 班级,语文 成绩 from @t union all
select 班级,数学 from @t union all
select 班级,英语 from @t ) t
group by left(班级,3)
/*
年级 各科平均成绩
------ -----------
二年级 80
六年级 79
三年级 79
四年级 80
五年级 81
一年级 79
(6 行受影响)
*/
select 班级, avg(成绩) 各科平均成绩
from
(select 班级,语文 成绩 from @t union all
select 班级,数学 from @t union all
select 班级,英语 from @t ) t
group by 班级
/*
班级 各科平均成绩
---------- -----------
二年级1班 80
六年级1班 80
六年级2班 77
三年级1班 82
三年级2班 76
四年级1班 77
四年级2班 84
五年级1班 81
一年级1班 81
一年级2班 78
(10 行受影响)
*/
select 班级,avg(语文),avg(数学),avg(英语)
from tableName
group by 班级
select 年级,avg(语文),avg(数学),avg(英语)
from tableName
group by 年级
select SUBSTRING(班级,1,3) as '年级',avg(语文),avg(数学),avg(英语) from Tables
group by 年级