CREATE TABLE Subject
( SubjectNo char(2) CONSTRAINT PK_Subject PRIMARY KEY,
SubjectName char(8) NOT NULL UNIQUE,
SubjectType char(4) CHECK( SubjectType IN ('主科','副科') ),
SubjectHour tinyint
)
GO
CREATE TABLE Teacher
( Tno char(4) CONSTRAINT PK_Teacher PRIMARY KEY,
Tname char(8) NOT NULL UNIQUE,
Tsex char(2) DEFAULT '女' CHECK( Tsex IN ('男','女') ),
Tage tinyint,
Twage tinyint,
Tsubject char(2) CONSTRAINT FK_Teacher_Tsubject FOREIGN KEY REFERENCES Subject(SubjectNo),
CHECK( Tage>Twage )
)
GO
CREATE TABLE Class
( ClassNo char(4) CONSTRAINT PK_Class PRIMARY KEY,
ClassName char(10) NOT NULL UNIQUE,
Tno char(4) NOT NULL,
CONSTRAINT FK_Class_Tno FOREIGN KEY (Tno) REFERENCES Teacher(Tno)
)
GO
CREATE TABLE C_S_T
( ClassNo char(4) CONSTRAINT FK_C_S_T_ClassNo FOREIGN KEY REFERENCES Class(ClassNo),
SubjectNo char(2) CONSTRAINT FK_C_S_T_SubjectNo FOREIGN KEY REFERENCES Subject(SubjectNo),
Tno char(4) CONSTRAINT FK_C_S_T_Tno FOREIGN KEY REFERENCES Teacher(Tno),
Semester char(6) NOT NULL,
Remark Decimal(3,1),
CONSTRAINT PK_C_S_T PRIMARY KEY (ClassNo,SubjectNo,Semester)
)
GO
CREATE TABLE Student
( Sno char(5) CONSTRAINT PK_Student PRIMARY KEY,
Sname char(8) NOT NULL,
Ssex char(2) CHECK( Ssex IN ('男','女') ),
Sage tinyint CHECK( Sage<18 ),
ClassNo char(4) CONSTRAINT FK_Student_ClassNo FOREIGN KEY REFERENCES Class(ClassNo),
Sstatus char(1) DEFAULT 'T'
)
GO
CREATE TABLE Exam_Score
( Semester char(6) NOT NULL,
Sno char(5) CONSTRAINT FK_Exam_Score_Sno FOREIGN KEY REFERENCES Student(Sno),
SubjectNo char(2) CONSTRAINT FK_Exam_Score_SubjectNo FOREIGN KEY REFERENCES Subject(SubjectNo),
Mscore tinyint CHECK( Mscore BETWEEN 0 AND 100 ),
Fscore tinyint CHECK( Fscore BETWEEN 0 AND 100 ),
Tscore tinyint CHECK( Tscore BETWEEN 0 AND 100 ),
Etype char(4) CHECK( Etype IN ('校考','區考','市考') ),
CONSTRAINT PK_Exam_Score PRIMARY KEY (Semester,Sno,SubjectNo)
這是六張表
現在查詢14001號同學的2014-1學期考試的科目名稱、任教老師姓名和總評成績（Exam_Score.Tscore）
我想創一個視圖連接6張表格但出現了問題
CREATE VIEW V_14001
AS SELECT SubjectName,Tname,Mscore,Fscore,Tscore
From Subject,C_S_T,Teacher,Class,Student,Exam_Score
WHERE Subject.SubjectNo=C_S_T.SubjectNo and C_S_T.Tno=Teacher.Tno and Teacher.Tno=Class.Tno and
Class.ClassNo=Student.ClassNo and Student.Sno=Exam_Score.Sno
請問是為什么
uj5u.com熱心網友回復：
錯誤資訊是什么？uj5u.com熱心網友回復：
沒有錯誤資訊，能夠正確執行，但這個結果不正確，語文和思平包括后面數學的老師都顯示成了趙亮。請問這是什么原因呢？轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/90551.html
標籤：疑難問題
上一篇：sql檔案mdf怎么改為通用路徑