tb1(学号,姓名) tb2(科目ID,科目名称) tb3(学号,科目ID,成绩) 要查询结果成下面的样子: 学号,姓名,数学,语文,英语,总分,及格率select tb1.学好,tb1.姓名, (select 成绩 from tb3,tb2 where tb3.学号=tb1.学号 and tb3.科目ID=tb2.科目ID and tb2.科目名称='数学')as 数学, (select 成绩 from tb3,tb2 where tb3.学号=tb1.学号 and tb3.科目ID=tb2.科目ID and tb2.科目名称='语文')as 语文, ...from tb1这样试一下
select tb1.学好,tb1.姓名, (select 成绩 from tb3,tb2 where tb3.学号=tb1.学号 and tb3.科目ID=tb2.科目ID and tb2.科目名称='数学')as 数学, (select 成绩 from tb3,tb2 where tb3.学号=tb1.学号 and tb3.科目ID=tb2.科目ID and tb2.科目名称='语文')as 语文, ... (select sum(成绩) from tb3 where tb3.学号=tb1.学号 )as 总分, (select count(*) from tb3 where tb3.学号=tb1.学号 and tb3.成绩>=60)/3.0 as 及格率from tb1
tb2(科目ID,科目名称)
tb3(学号,科目ID,成绩)
要查询结果成下面的样子:
学号,姓名,数学,语文,英语,总分,及格率select tb1.学好,tb1.姓名,
(select 成绩 from tb3,tb2 where tb3.学号=tb1.学号 and tb3.科目ID=tb2.科目ID and tb2.科目名称='数学')as 数学,
(select 成绩 from tb3,tb2 where tb3.学号=tb1.学号 and tb3.科目ID=tb2.科目ID and tb2.科目名称='语文')as 语文,
...from tb1这样试一下
(select 成绩 from tb3,tb2 where tb3.学号=tb1.学号 and tb3.科目ID=tb2.科目ID and tb2.科目名称='数学')as 数学,
(select 成绩 from tb3,tb2 where tb3.学号=tb1.学号 and tb3.科目ID=tb2.科目ID and tb2.科目名称='语文')as 语文,
...
(select sum(成绩) from tb3 where tb3.学号=tb1.学号 )as 总分,
(select count(*) from tb3 where tb3.学号=tb1.学号 and tb3.成绩>=60)/3.0 as 及格率from tb1