帮忙写个视图(sql)

xaojancsdn 2004-03-22 05:46:04

一个学生表：
id 毕业年份(year) 年级(nj) 班级(bj)

一成绩表：
id (与学生表id关联) 语文成绩(cj)
如何得到下列查询结果：

毕业年份年级班级全班人数全年级人数班参加考试人数(BckNumber)
年级参考人数(nCknumber) 班补考率（<60分)(Nbkl) 年级补考率(bbkl)
补考率=补考人数/参加考试人数

...全文

30 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

xaojancsdn 2004-03-23

打赏
举报

测试通过，但如果是很多科的成绩我应该改动哪个地方，比如说还有其它科成绩。
成绩表：
id (与学生表id关联) 语文成绩(cj)，数学(sx)....

毕业年份科目年级班级全班人数全年级人数班参加考试人数年级参考人数数班补考率年级补考
（分不够说就是）

zjcxc 2004-03-22

打赏
举报

--测试

--测试数据
create table 学生表(id int,nj varchar(10),bj varchar(10),[year] int)
insert 学生表
select 1,'01级','02班',2004
union all select 2,'02级','01班',2005

create table 成绩表(id int,cj int,xueNian int)
insert 成绩表
select 1,98,2004
union all select 2,20,2005
go

--查询
select 毕业年份=a.[year],年级=a.nj,班级=a.bj
,全班人数=count(a.id)
,全年级人数=(select count(id) from 学生表 where nj=a.nj and [year]=a.[year])
,班参加考试人数=count(b.id)
,年级参考人数数=(select count(*) from 学生表 aa
join 成绩表 bb on aa.[year]=bb.[xueNian] and aa.id=bb.id
where aa.nj=a.nj and aa.[year]=a.[year])
,班补考率=case count(b.id) when 0 then '0.00%' else
cast(cast(sum(case when b.cj<60 then 100.0 else 0.0 end)/
count(b.id) as decimal(20,2)) as varchar)+'%' end
,年级补考率=case (select count(*) from 学生表 aa
join 成绩表 bb on aa.[year]=bb.[xueNian] and aa.id=bb.id
where aa.nj=a.nj and aa.[year]=a.[year])
when 0 then '0.00%' else
cast(cast((select sum(case when bb.cj<60 then 100.0 else 0.0 end)
from 学生表 aa
join 成绩表 bb on aa.[year]=bb.[xueNian] and aa.id=bb.id
where aa.nj=a.nj and aa.[year]=a.[year])
/(select count(*) from 学生表 aa
join 成绩表 bb on aa.[year]=bb.[xueNian] and aa.id=bb.id
where aa.nj=a.nj and aa.[year]=a.[year])
as decimal(20,2)) as varchar)+'%' end
from 学生表 a
left join 成绩表 b on a.[year]=b.[xueNian] and a.id=b.id
group by a.[year],a.nj,a.bj
go

--删除测试
drop table 学生表,成绩表

/*--测试结果
毕业年份年级班级全班人数全年级人数班参加考试人数年级参考人数数班补考率年级补考率
----------- ---------- ---------- ----------- ----------- ----------- ----------- ------------------------------- -------------------------------
2004 01级 02班 1 1 1 1 0.00% 0.00%
2005 02级 01班 1 1 1 1 100.00% 100.00%

（所影响的行数为 2 行）
--*/

zjcxc 2004-03-22

打赏
举报

--上面错了:

--查询
select 毕业年份=a.[year],年级=a.nj,班级=a.bj
,全班人数=count(a.id)
,全年级人数=(select count(id) from 学生表 where nj=a.nj and [year]=a.[year])
,班参加考试人数=count(b.id)
,年级参考人数数=(select count(*) from 学生表 aa
join 成绩表 bb on aa.[year]=bb.[xueNian] and aa.id=bb.id
where aa.nj=a.nj and aa.[year]=a.[year])
,班补考率=case count(b.id) when 0 then '0.00%' else
cast(cast(sum(case when b.cj<60 then 100.0 else 0.0 end)/
count(b.id) as decimal(20,2)) as varchar)+'%' end
,年级补考率=case (select count(*) from 学生表 aa
join 成绩表 bb on aa.[year]=bb.[xueNian] and aa.id=bb.id
where aa.nj=a.nj and aa.[year]=a.[year])
when 0 then '0.00%' else
cast(cast((select sum(case when bb.cj<60 then 100.0 else 0.0 end)
from 学生表 aa
join 成绩表 bb on aa.[year]=bb.[xueNian] and aa.id=bb.id
where aa.nj=a.nj and aa.[year]=a.[year])
/(select count(*) from 学生表 aa
join 成绩表 bb on aa.[year]=bb.[xueNian] and aa.id=bb.id
where aa.nj=a.nj and aa.[year]=a.[year])
as decimal(20,2)) as varchar)+'%' end
from 学生表 a
left join 成绩表 b on a.[year]=b.[xueNian] and a.id=b.id
group by a.[year],a.nj,a.bj

caiyunxia 2004-03-22

打赏
举报

修改
declare @student table (id varchar(10),毕业年份 varchar(10),年级 varchar(10),班级 varchar(10) )
declare @cj table (id varchar(10),语文成绩 int,学年 varchar(10) )
insert into @student values ('1','2006','01级','02班')
insert into @student values ('2','2007','02级','01班')
insert into @student values ('2','2006','01级','01班')
insert into @cj values ('1',98,'2004')
insert into @cj values ('2',20,'2005')
insert into @cj values ('2',20,'2004')
select distinct 毕业年份,年级,班级
,
(select count(*) from @student where 班级=t1.班级 and 毕业年份=t1.毕业年份) 全班人数,
(select count(*) from @student where 毕业年份=t1.毕业年份) 全年级人数,
(select count(*) from @cj t3,@student t4 where t3.id=t4.id and t4.班级=t1.班级 and t4.毕业年份=t1.毕业年份)班参加考试人数,
(select count(*) from @cj t3,@student t4 where t3.id=t4.id and t4.毕业年份=t1.毕业年份)年级参考人数,
(select count(*) from @cj t3,@student t4 where t3.id=t4.id and t4.班级=t1.班级 and t4.毕业年份=t1.毕业年份 and t3.语文成绩<60)
*1.0/ (select count(*) from @cj t3,@student t4 where t3.id=t4.id and t4.班级=t1.班级 and t4.毕业年份=t1.毕业年份) 班补考率,
1.0*(select count(*) from @cj t3,@student t4 where t3.id=t4.id and t4.毕业年份=t1.毕业年份 and t3.语文成绩<60)/
(select count(*) from @cj t3,@student t4 where t3.id=t4.id and t4.毕业年份=t1.毕业年份) 年级补考率
from @student t1

/*
2006 01级 01班 1 2 2 3 1.000000000000 .666666666666
2006 01级 02班 1 2 1 3 .000000000000 .666666666666
2007 02级 01班 1 1 2 2 1.000000000000 1.000000000000
？

zjcxc 2004-03-22

打赏
举报

select 毕业年份=a.[year],年级=a.nj,班级=a.bj
,全班人数=count(a.id)
,全年级人数=(select count(id) from 学生表 where nj=a.nj)
,班参加考试人数=count(b.id)
,年级参考人数数=(select count(id) from 成绩表 where nj=a.nj)
,班补考率=case count(b.id) when 0 then '0.00%' else
cast(cast(sum(case when b.cj<60 then 100.0 else 0.0 end)/
count(b.id) as decimal(20,2)) as varchar)+'%' end
,年级补考率=case (select count(id) from 成绩表 where nj=a.nj)
when 0 then '0.00%' else
cast(cast((select sum(case when cj<60 then 100.0 else 0.0 end) from 成绩表 where nj=a.nj)
/(select count(id) from 成绩表 where nj=a.nj)
as decimal(20,2)) as varchar)+'%' end
from 学生表 a
left join 成绩表 b on a.[year]=b.[year] and a.id=b.id

solidpanther 2004-03-22

打赏
举报

declare @student table (id varchar(10),毕业年份 varchar(10),年级 varchar(10),班级 varchar(10) )
declare @cj table (id varchar(10),语文成绩 int,学年 varchar(10) )
insert into @student values ('1','2006','01级','02班')
insert into @student values ('2','2007','02级','01班')
insert into @student values ('2','2006','01级','01班')
insert into @cj values ('1',98,'2004')
insert into @cj values ('2',20,'2005')
insert into @cj values ('2',20,'2004')
select distinct 毕业年份,年级,班级
,
(select count(*) dd from @student where 班级=t1.班级 and 毕业年份=t1.毕业年份) 全班人数,
(select count(*) ddd from @student where 年级=t1.年级 and 毕业年份=t1.毕业年份) 全年级人数,
(select count(*) dddd from @cj t3,@student t4 where t3.id=t4.id and t4.班级=t1.班级 and t4.毕业年份=t1.毕业年份)班参加考试人数,
(select count(*) ddddd from @cj t3,@student t4 where t3.id=t4.id and t4.年级=t1.年级 and t4.毕业年份=t1.毕业年份)年级参考人数,
(select count(*) dddd from @cj t3,@student t4 where t3.id=t4.id and t4.班级=t1.班级 and t4.毕业年份=t1.毕业年份 and t3.语文成绩<60)班补考率,
(select count(*) ddddd from @cj t3,@student t4 where t3.id=t4.id and t4.年级=t1.年级 and t4.毕业年份=t1.毕业年份 and t3.语文成绩<60)年级补考率
from @student t1

xaojancsdn 2004-03-22