資料庫 UPDATE多條記錄不同值，同時UPDATE多個欄位，update多條

資料庫 UPDATE多條記錄不同值，同時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 Beta1MySQL - 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 `score`(`id`,`studentId`,`subjectName`,`score`,`examTime`) values (1,1,'語文',70,'2015-02-26 18:11:39'),(2,1,'數學',80,'2015-03-26 18:11:50'),(3,1,'英語',76,'2015-04-26 18:11:56'),(4,1,'曆史',96,'2015-05-26 18:12:02'),(5,2,'語文\r\n數學\r\n英語\r\n曆史\r\n語文',84,'2015-02-26 18:11:39'),(6,2,'數學',56,'2015-03-26 18:11:50'),(7,2,'英語',86,'2015-04-26 18:11:56'),(8,2,'曆史',45,'2015-05-26 18:12:02'),(9,3,'語文',87,'2015-02-26 18:11:39'),(10,3,'數學',98,'2015-03-26 18:11:50'),(11,3,'英語',67,'2015-04-26 18:11:56'),(12,3,'曆史',86,'2015-05-26 18:12:02'),(13,4,'語文',97,'2015-02-26 18:11:39'),(14,4,'數學',68,'2015-03-26 18:11:50'),(15,4,'英語',79,'2015-04-26 18:11:56'),(16,4,'曆史',83,'2015-05-26 18:12:02'),(17,5,'語文',92,'2015-02-26 18:11:39'),(18,5,'數學',93,'2015-03-26 18:11:50'),(19,5,'英語',65,'2015-04-26 18:11:56'),(20,5,'曆史',88,'2015-05-26 18:12:02'),(21,6,'語文',87,'2015-01-05 18:48:48'),(22,6,'數學',67,'2015-01-05 18:48:48'),(23,6,'英語',99,'2015-01-05 18:48:48'),(24,6,'曆史',88,'2015-01-05 18:48:48');/*Table structure for table `student` */DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',  `name` varchar(20) DEFAULT NULL COMMENT '姓名',  `score_sum` varchar(20) DEFAULT NULL COMMENT '總成績',  `score_avg` varchar(20) DEFAULT NULL COMMENT '平均成績',  `age` int(11) DEFAULT NULL COMMENT '1男0女',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;/*Data for the table `student` */insert  into `student`(`id`,`name`,`score_sum`,`score_avg`,`age`) values (1,'小明','252','84',1),(2,'小王','187','62.3',1),(3,'莉莉','','',0),(4,'柱子','230','76.7',1),(5,'大毛','','',0),(6,'亮子','0','0',1);/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;