数据库UPDATE多条记录不同值，同时UPDATE多个字段 - 数据库编程

需求

如下两张表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

/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.5.28 : Database - test
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

/*Table structure for table `score` */

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `studentId` int(11) DEFAULT NULL COMMENT '学员ID',
  `subjectName` varchar(20) DEFAULT NULL COMMENT '科目名称',
  `score` float DEFAULT NULL COMMENT '考试成绩',
  `examTime` datetime DEFAULT NULL COMMENT '考试时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;

/*Data for the table `score` */

insert  into

数据库UPDATE多条记录不同值，同时UPDATE多个字段(一)