一、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");