SQL汇总行

一、MySQL

1、结果如图所示：

2、sql语句
说明：UNION 操作符选取不同的值；UNION ALL 操作符允许重复的值。
详见SQL UNION 和 UNION ALL 操作符

SELECT Id,Score,Course,Student FROM scores 
UNION ALL
SELECT "总分",SUM( Score ),"","" FROM scores

二、Oracle

1、结果如图所示：

2、sql语句

SELECT TO_CHAR(ID) ID,"SCORE","COURSE","STUDENT" FROM "scores"
UNION ALL
SELECT '总分',SUM(SCORE),'','' FROM "scores" 

三、附录：表数据

1、MySQL成绩表的sql文件内容如下：

/*
 Navicat Premium Data Transfer

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 80020
 Source Host           : localhost:3306
 Source Schema         : mysql

 Target Server Type    : MySQL
 Target Server Version : 80020
 File Encoding         : 65001

 Date: 15/03/2021 22:30:36
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for scores
-- ----------------------------
DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores`  (
  `Id` int(0) NOT NULL,
  `Score` int(0) NULL DEFAULT NULL,
  `Course` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Student` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of scores
-- ----------------------------
INSERT INTO `scores` VALUES (1, 80, '语文', 'Jack');
INSERT INTO `scores` VALUES (2, 79, '数学', 'Jack');
INSERT INTO `scores` VALUES (3, 100, '英语', 'Jack');
INSERT INTO `scores` VALUES (4, 100, '语文', 'Sally');
INSERT INTO `scores` VALUES (5, 79, '数学', 'Sally');
INSERT INTO `scores` VALUES (6, 99, '地理', 'Bob');
INSERT INTO `scores` VALUES (7, NULL, NULL, NULL);

SET FOREIGN_KEY_CHECKS = 1;

2、Oracle成绩表的sql文件内容如下：

DROP TABLE "IMESAPP"."scores";
CREATE TABLE "IMESAPP"."scores" (
  "ID" NUMBER NOT NULL ,
  "SCORE" NUMBER ,
  "COURSE" VARCHAR2(255 BYTE) ,
  "STUDENT" VARCHAR2(255 BYTE) 
)
TABLESPACE "MES_DATA"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;

-- ----------------------------
-- Records of scores
-- ----------------------------
INSERT INTO "IMESAPP"."scores" VALUES ('1', '80', '语文', 'Jack');
INSERT INTO "IMESAPP"."scores" VALUES ('2', '79', '数学', 'Jack');
INSERT INTO "IMESAPP"."scores" VALUES ('3', '100', '英语', 'Jack');
INSERT INTO "IMESAPP"."scores" VALUES ('4', '100', '语文', 'Sally');
INSERT INTO "IMESAPP"."scores" VALUES ('5', '79', '数学', 'Sally');
INSERT INTO "IMESAPP"."scores" VALUES ('6', '99', '地理', 'Bob');
INSERT INTO "IMESAPP"."scores" VALUES ('7', NULL, NULL, NULL);

-- ----------------------------
-- Primary Key structure for table scores
-- ----------------------------
ALTER TABLE "IMESAPP"."scores" ADD CONSTRAINT "SYS_C00147267" PRIMARY KEY ("ID");