gpt4 book ai didi

sql - 我不知道这个功能有什么问题

转载 作者:搜寻专家 更新时间:2023-10-30 22:19:49 26 4
gpt4 key购买 nike

嘿...我有这个数据库,我需要创建一个函数来返回通过某个科目的学生的百分比,但我不知道我做错了什么...你能帮帮我吗???这是我的图表 Diagram这是我的功能...

create function fnPassedStudents(@Semester varchar(7),@CodSubjects varchar(5))
returns @Passed
TABLE (
Semester varchar(7),
Cod_Subjects varchar(5),
Name_Subjects varchar(80),
Nro_Students int,
Nro_Passed float,
Nro_Failed float,
PercentagePassed varchar(4))
as
begin
declare @NroPassed float
select @NroPassed = count(M.Cod_Student)
from Matricula M inner join Subjects A on M.Cod_Subjects = A.Cod_Subjects
Where M.Semester=@Semester and M.Cod_Subjects = @CodSubjects and M.Grade>=10

insert into @Passed
select M.Semester, A.Cod_Subjects, A.Name_Subjects, COUNT(M.Cod_Student) as Total,
@NroPassed as Passed, (COUNT(M.Cod_Student) - @NroPassed) as Failed,
((@NroPassed * 100)/(count(M.Cod_Student))) as PercentagePassed
from Matricula M inner join Subjects A on M.Cod_Subjects = A.Cod_Subjects
Group by M.Semester, A.Cod_Subjects, A.Name_Subjects
return
end
go

我不确定这些信息是否足够...但请耐心等待,我会添加您需要的任何内容...谢谢!!!

最佳答案

create function fnPassedStudents(@Semester varchar(7),@CodSubjects varchar(5))
returns @Passed
TABLE (
Semester varchar(7),
Cod_Subjects varchar(5),
Name_Subjects varchar(80),
Nro_Students int,
Nro_Passed float,
Nro_Failed float,
PercentagePassed varchar(4))
as
begin
insert into @Passed
select
M.Semester,
A.Cod_Subjects,
A.Name_Subjects,
count(distinct M.cod_student),
count(case when M.Grade>=10 then 1 end),
count(*) - count(case when M.Grade>=10 then 1 end),
count(case when M.Grade>=10 then 1 end) / count(distinct M.cod_student) * 100
from Matricula M
inner join Subjects A on M.Cod_Subjects = A.Cod_Subjects
Group by M.Semester, A.Cod_Subjects, A.Name_Subjects
return
end
go

成分

count(distinct M.cod_student) - 参加一个学期/科目的学生人数case when M.Grade>=10 then 1 end - 通过时生成 1,否则为 null。 NULL 不计算在内count(*) - count(case..) - passed = failed 的补码

关于sql - 我不知道这个功能有什么问题,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/5293956/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com