连接查询，视图

来源：互联网发布：godaddy域名转到万网编辑：程序博客网时间：2024/05/19 00:10

表Member:

表F:

表score:

内连接查询：

SELECT Mname,Fname,Score FROM Member INNER JOIN score ON Member.MID=score.MID INNER JOIN F ON F.FID=score.FID

视图的创建，查询

IF EXISTS(SELECT *FROM sysobjects WHERE id=OBJECT_ID('Student_View'))DROP VIEW Student_ViewgoCREATE VIEW Student_View ASSELECT Mname,Fname,Score FROM Member INNER JOIN score ON Member.MID=score.MID INNER JOIN F ON F.FID=score.FID  SELECT *FROM Student_View

查询每个人的总分

SELECT MName,total.total_score FROM Member INNER JOIN (SELECT MID,SUM(Score) total_score FROM score GROUP BY MID)  totalON Member.MID=total.MID

带参数的存储过程：

IF EXISTS(SELECT *FROM sysobjects WHERE id=OBJECT_ID('student_SumScore'))DROP PROCEDURE student_SumScoreCREATE PROCEDURE student_SumScore(@mid CHAR(10))ASSET NOCOUNT OFFBEGINSELECT MName,total.total_score FROM Member INNER JOIN (SELECT MID,SUM(Score) total_score FROM score GROUP BY MID)  totalON Member.MID=total.MID WHERE Member.MID=@midEND SET NOCOUNT ONEXEC student_SumScore 'M001'

0 0