晕,这么长啊,没空帮你写了,不过你可以试试这样的方式:
select
[select list]
from
[table name]
where
[Condition]如:
select
(select A.ID from A where A.Name=B.Name)
(select C.ID from C where C.Name=B.Name)
...
from
B
where
[condition]这样可以选出在联合查询中某一表没有记录的情况,这时候查询出的记录值用null来代替,然后再用sum()应该可以达到你要的效果
select
[select list]
from
[table name]
where
[Condition]如:
select
(select A.ID from A where A.Name=B.Name)
(select C.ID from C where C.Name=B.Name)
...
from
B
where
[condition]这样可以选出在联合查询中某一表没有记录的情况,这时候查询出的记录值用null来代替,然后再用sum()应该可以达到你要的效果
Create PROCEDURE sx_StudentScoreReport@xuexiaoID varchar(12) --学校编号(代码)AS
BEGIN
select
a. StudentID, ----学生编号
a. Name, ----学生姓名
d. CourseTypeName, ---课程类型
sum(b. Score ) --各个类型课程的总学分
from
StudentScore b
LEFT JOIN Student a ON(b.StudentID = a.StudentID)
LEFT JOIN Course c ON(b.CourseID = c. CourseID)
LEFT JOIN CourseType d ON(c. CourseTypeID = d. CourseTypeID)
where
a. SchoolID = @xuexiaoID GROUP BY
a. StudentID,
a. Name,
d. CourseTypeName,
order by
a. StudentID
END
a. StudentID, ----学生编号
a. Name, ----学生姓名
d. CourseTypeName, ---课程类型
ISNULL('--',sum(b. Score )) --各个类型课程的总学分
from
StudentScore b
LEFT JOIN Student a ON(b.StudentID = a.StudentID)
LEFT JOIN Course c ON(b.CourseID = c. CourseID)
LEFT JOIN CourseType d ON(c. CourseTypeID = d. CourseTypeID)
where
a. SchoolID = @xuexiaoID GROUP BY
a. StudentID,
a. Name,
d. CourseTypeName,
order by
a. StudentID
Create PROCEDURE sx_StudentScoreReport@xuexiaoID varchar(12) --学校编号(代码)AS
BEGIN
select
a. StudentID, ----学生编号
a. Name, ----学生姓名
(select CourseTypeName from CourseType where CourseTypeID=C.CourseTypeID) as CourseTypeName, ---课程类型
sum(b. Score) --各个类型课程的总学分
from
Student a inner join StudentScore b on a. StudentID = b. StudentID inner join Course c on b. CourseID = c. CourseID
where
a. SchoolID = @xuexiaoID
GROUP BY
a. StudentID,
a. Name,
CourseTypeName
order by
a. StudentID
END我只是说大概哦,没有调试过,你先调试,可能会有小的问题自己改改吧,不行了要迟到了,我去开会了
a. StudentID, ----学生编号
a. Name, ----学生姓名
d. CourseTypeName, ---课程类型
ISNULL(sum(b. Score ),0) --各个类型课程的总学分
from
Student a
LEFT JOIN StudentScore b ON(b.StudentID = a.StudentID)
inner JOIN Course c ON(b.CourseID = c. CourseID)
inner JOIN CourseType d ON(c. CourseTypeID = d. CourseTypeID)
where
a. SchoolID = @xuexiaoID GROUP BY
a. StudentID,
a. Name,
d. CourseTypeName,
order by
a. StudentID
StudentID varchar(10),
[Name] nvarchar(40),
SchoolID varchar(12)
)create table CourseType(
CourseTypeID int,
CourseTypeName nvarchar(40))create table Course(
CourseID int,
CourseName nvarchar(40),
CourseTypeID int)create table StudentScore(
StudentID varchar(10),
CourseID int,
Score decimal(9,2))
insert into student
select '20040101',N'小明','1'
union select '20040102',N'小王','1'insert into CourseType
select 1,N'选修课'
union select 2,N'必须课'
union select 3,N'限定课'insert into Course
select 101,N'语文',1
union select 102,N'英语',2insert into StudentScore
select '20040101',101,10GOCreate proc sx_StudentScoreReport
@xuexiaoID varchar(12)
ASselect X.StudentID,X.Name,X.CourseTypeName,ISNULL(Y.Score,0) Score from(select StudentID,Name,CourseTypeName
from student,CourseType where SchoolID=@xuexiaoID) X LEft outer join
(select a.StudentID,CourseTypeName,sum(b.Score) Score
from student a left Outer join StudentScore b on a.StudentID=b.StudentID and SchoolID=@xuexiaoID
left outer join Course c on isnull(b.CourseID,'')=isnull(b.CourseID,'')
left OUter Join CourseType d on isnull(c.CourseTypeID,'')=isnull(d.CourseTypeID,'')
Group by a.StudentID,CourseTypeName) Y
ON X.StudentID=Y.StudentID and X.CourseTypeName=Y.CourseTypeNameGOExec sx_StudentScoreReport '1'GO
drop table student
drop table CourseType
drop table Course
drop table StudentScore
drop proc sx_StudentScoreReport
BEGIN
select i.studentid,i.name,i.courseTypeName,sum(i.score) as score
from
(select a.studentid,a.name,b.CourseTypeID,b.CourseTypeName ,0 as score
from student as a,CourseType as b
where a. SchoolID = @xuexiaoID
union all
select a. StudentID, ----学生编号
a. Name, ----学生姓名
d.CourseTypeID,
d. CourseTypeName, ---课程类型
sum(b. Score ) --各个类型课程的总学分
from Student a, StudentScore b, Course c, CourseType d
where
a. SchoolID = @xuexiaoID
and a. StudentID = b. StudentID
and b. CourseID = c. CourseID
and c. CourseTypeID = d. CourseTypeID
GROUP BY
a. StudentID,
a. Name,
d.CourseTypeID,
d. CourseTypeName
) as i
group by i.studentid,i.name,i.CourseTypeName
order by i.studentid
END
--Debug
studentid name courseTypeName score
----------- -------------------- -------------------- ----------------------------------------
200110101 李明 必须课 .0
200110101 李明 限定课 .0
200110101 李明 选修课 10.0