需求
如下两张表student(学生表)、score(测试成绩表)
现需要统计:2015-03-10日之后,性别 age=1 的测试成绩的 总分 与 平均分。
要求:使用一个SQL统计score表,将结果更新到student表的score_sum和score_avg字段中。
结果如图:
实现:
如果我们只需要更新一个字段,MySQL和Oracle语法是一样的,在 set 后面跟一个子查询即可,如下:
UPDATE student D
SET D.score_sum =
(
SELECT
SUM(B.score)
FROM score B
WHERE B.studentId = D.id
AND b.examTime >= '2015-03-10'
GROUP BY B.studentId
)
WHERE D.id =
(
SELECT
E.id FROM
(
SELECT
DISTINCT a.studentId AS id
FROM score A
WHERE A.examTime >= '2015-03-10'
) E
WHERE E.id = D.id
)
AND d.age = 1;
现在我们需要同时更新2个字段,最不经过大脑思考的方法就是 “为每个 set 后面都跟一个子查询”,
假如我们要 set 十个字段或者更多字段呢?很显然,这样在性能上是很不合适的方法。
同时更新多个字段在mysql和oracle中的方法是不一样,MYSQL需要连接表,ORACLE使用 set(...) 即可
(看了下面的SQL你会发现,还是ORACLE简单易用、易懂)
1) MYSQL 实现我们最终的需求,语句如下:
UPDATE student D
LEFT JOIN (SELECT
B.studentId,
SUM(B.score) AS s_sum,
ROUND(AVG(B.score),1) AS s_avg
FROM score B
WHERE b.examTime >= '2015-03-10'
GROUP BY B.studentId) C
ON (C.studentId = D.id)
SET D.score_sum = c.s_sum,
D.score_avg = c.s_avg
WHERE D.id =
(
SELECT
E.id FROM
(
SELECT
DISTINCT a.studentId AS id
FROM score A
WHERE A.examTime >= '2015-03-10'
) E
WHERE E.id = D.id
)
AND d.age = 1;
2) ORACLE 实现我们最终的需求,语句如下:
UPDATE student D
SET (D.score_sum, D.score_avg) = (
SELECT
SUM(B.score) AS s_sum,
ROUND(AVG(B.score),1) AS s_avg
FROM score B
WHERE b.examTime >= '2015-03-10'
AND B.studentId = D.id
GROUP BY B.studentId
)
WHERE D.id =
(
SELECT
E.id FROM
(
SELECT
DISTINCT a.studentId AS id
FROM score A
WHERE A.examTime >= '2015-03-10'
) E
WHERE E.id = D.id
)
AND d.age = 1;
本文中用到的2个知识点:
1、更新多条记录,每条记录不同值。
2、同时更新多个字段的方法。
===== 将 age = 1 并且没有测试成绩的同学给予默认值0,调整SQL如下 =====
UPDATE student D
LEFT JOIN (SELECT
B.studentId,
SUM(B.score) AS s_sum,
ROUND(AVG(B.score),1) AS s_avg
FROM score B
WHERE b.examTime >= '2015-03-10'
GROUP BY B.studentId) C
ON (C.studentId = D.id)
SET D.score_sum = IFNULL(c.s_sum,0),
D.score_avg = IFNULL(c.s_avg,0)
WHERE D.id =
(
SELECT
E.id FROM
(
SELECT
DISTINCT a.studentId AS id
FROM score A
##WHERE A.examTime >= '2015-03-10'
) E
WHERE E.id = D.id
)
AND d.age = 1;
结果如下:
Test SQL