create ?? table ?學生表

(??
xno? int ?,
?xname? varchar ( 20 )?

)
go ?



insert ? into ?dbo.學生表?
select ? 1 , ' 小王 '
union ? all ?
select ? 2 , ' 小黑 ' ?


create ? table ?課程表
(
kno? int ?,
kname? varchar ( 20 )

)
go

insert ? into ?課程表
select ? 1 , ' 語文 '
union ? all
select ? 2 , ' 數學 '





create ? table ?成績表
(
cno? int ,
ckno? int ,
cf? float
)




alter ? table ?成績表
alter ? column ?cf?

insert ? into ?成績表
select ? 1 , 1 , 86.5


insert ? into ?成績表
select ? 1 , 2 , 98.86

union ? all

select ? 2 , 1 , 86.7
union ? all
select ? 2 , 2 , 86.9




select ? * ? from ?學生表
select ? * ? from ?課程表
select ? * ? from ?成績表

SELECT ?B.xname,
( SELECT ?cf? FROM ?成績表? INNER ? JOIN ?課程表? ON ?成績表.ckno = 課程表.kno? WHERE ?成績表.cno = B.xno? AND ?課程表.kname = ' 語文 ' ?)? AS ?語文,
( SELECT ?cf? FROM ?成績表? INNER ? JOIN ?課程表? ON ?成績表.ckno = 課程表.kno? WHERE ?成績表.cno = B.xno? AND ?課程表.kname = ' 數學 ' ?)? AS ?數學
FROM ?學生表?B

SELECT ?xname,
MAX ( CASE ?kname? WHEN ? ' 語文 ' ? THEN ?cf? ELSE ? 0 ? END )? AS ?語文,
MAX ( CASE ?kname? WHEN ? ' 數學 ' ? THEN ?cf? ELSE ? 0 ? END )? AS ?數學
FROM ?( SELECT ?B.xname,C.kname,D.cf? FROM ?成績表?D?
INNER ? JOIN ?學生表?B? ON ?B.xno = D.cno?
INNER ? JOIN ?課程表?C? ON ?C.kno = D.ckno)? AS ?ZH? GROUP ? BY ?xname