56,678
社区成员
发帖
与我相关
我的任务
分享
update SC set score = (
select avg_score from
(select sc2.C_No,avg(score) avg_score
from Teacher t ,course c,sc sc2 where t.T_no=c.T_no and Tname='李四' and sc2.C_No=c.C_No
group by sc2.C_No
) a where a.C_No=SC.C_No)
mysql> update sc inner join (
-> select sc.C_No,avg(sc.score) as score
-> from course c inner join teacher t on c.T_No=t.T_N
-> inner join sc on c.C_No=sc.C_No
-> where t.Tname='李四'
-> group by sc.C_No
-> ) b on sc.C_No=b.C_No
-> set sc.score=b.score
-> ;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from sc;
+------+------+-------+
| S_No | C_No | score |
+------+------+-------+
| 1 | 1 | 88 |
| 1 | 2 | 54 |
| 2 | 1 | 88 |
| 2 | 2 | 54 |
| 3 | 4 | 94 |
+------+------+-------+
5 rows in set (0.00 sec)
mysql>
mysql> update sc a,(select t1.*,t2.Tname from (select a.*,b.Cname,b.T_No from (select C_No,avg(score) score from sc group by C_No) a ,course b where a.C_No=b.C_no) t1,teacher t2 where t1.T_No=t2.T_No) b set a.score =b.score where a.C_No=b.C_No;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from sc;
+------+------+-------+
| S_No | C_No | score |
+------+------+-------+
| 1 | 1 | 88 |
| 1 | 2 | 54 |
| 2 | 1 | 88 |
| 2 | 2 | 54 |
| 3 | 4 | 94 |
+------+------+-------+
5 rows in set (0.00 sec)
+------+------+-------+
| S_No | C_No | score |
+------+------+-------+
| 1 | 1 | 88 |
| 1 | 2 | 54 |
| 2 | 1 | 88 |
| 2 | 2 | 54 |
| 3 | 4 | 94 |
+------+------+-------+
/*
SQLyog Community Edition- MySQL GUI
MySQL - 5.1.42-community
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
create table `course` (
`C_No` double ,
`Cname` varchar (300),
`T_No` double
);
insert into `course` (`C_No`, `Cname`, `T_No`) values('1','Math','1');
insert into `course` (`C_No`, `Cname`, `T_No`) values('2','Chinese','1');
insert into `course` (`C_No`, `Cname`, `T_No`) values('3','Sport','3');
insert into `course` (`C_No`, `Cname`, `T_No`) values('4','Music','4');
create table `teacher` (
`T_No` double ,
`Tname` varchar (300)
);
insert into `teacher` (`T_No`, `Tname`) values('1','李四');
insert into `teacher` (`T_No`, `Tname`) values('2','王五');
insert into `teacher` (`T_No`, `Tname`) values('3','马六');
create table `student` (
`S_No` double ,
`Sname` varchar (300),
`Sage` double ,
`Ssex` double
);
insert into `student` (`S_No`, `Sname`, `Sage`, `Ssex`) values('1','Mike','20','1');
insert into `student` (`S_No`, `Sname`, `Sage`, `Ssex`) values('2','Jack','21','1');
insert into `student` (`S_No`, `Sname`, `Sage`, `Ssex`) values('3','Jerry','22','1');
insert into `student` (`S_No`, `Sname`, `Sage`, `Ssex`) values('4','Tom','23','1');
create table `sc` (
`S_No` double ,
`C_No` double ,
`score` double
);
insert into `sc` (`S_No`, `C_No`, `score`) values('1','1','96');
insert into `sc` (`S_No`, `C_No`, `score`) values('1','2','68');
insert into `sc` (`S_No`, `C_No`, `score`) values('2','1','80');
insert into `sc` (`S_No`, `C_No`, `score`) values('2','2','40');
insert into `sc` (`S_No`, `C_No`, `score`) values('3','4','94');
update sc,
(
select sc2.c_no,avg(sc2.score) as avg_score
from sc sc2 ,teacher t,course c
where sc2.c_no=c.c_no and c.t_no=t.t_no and t.tname='李四'
group by sc2.c_no
) b
where sc.c_no=b.c_no
set sc.score=b.avg_score