日期:2014-05-17 浏览次数:20758 次
with tb as(
select course.*,stu_course.sno,stu_course.score from stu_course join course on course.cid=stu_course.cid
)
select stu.*,t1.coursename+':'+rtrim(t1.score),t2.coursename+':'+rtrim(t2.score) from stu join tb t1 on t1.sno=stu.sno
join tb t2 on t1.sno=t2.sno where t1.coursename='数据库' and t2.coursename='java'
create table STU(
SNO int primary key, --学号
SNAME VARCHAR(20), --姓名
SAGE TINYINT , --年龄
SSEX CHAR(2) DEFAULT('女') --性别
)
INSERT INTO STU VALUES(111,'赵力平',20,'男');
INSERT INTO STU VALUES(112,'王雪',20,'女');
INSERT INTO STU VALUES(113,'佘明伟',20,'男');
create table COURSE(
CID INT IDENTITY(1,1) PRIMARY KEY,
TNAME varchar(20), --老师名称
coursename varchar(20) --课程名称
)
INSERT INTO COURSE VALUES('欧阳老师','数据库')
INSERT INTO COURSE VALUES('候老师','JAVA')
INSERT INTO COURSE VALUES('李老师','WEB')
CREATE TABLE STU_COURSE(
SCID INT IDENTITY(1,1) PRIMARY KEY,
SNO INT FOREIGN KEY(SNO) REFERENCES STU(SNO),
CID INT FOREIGN KEY(CID) REFERENCES COURSE(CID),
SCORE FLOAT
)
INSERT INTO STU_COURSE VALUES(111,1,78.5)
INSERT INTO STU_COURSE VALUES(111,3,8.5)
INSERT INTO STU_COURSE VALUES(112,1,7.5)
INSERT INTO STU_COURSE VALUES(112,2,8.5)
INSERT INTO STU_COURSE VALUES(112,3,9.5