Oracle 上下合并两张表格_oracle两张表纵向合并-CSDN博客

本文链接：https://blog.csdn.net/xiaoliang98/article/details/109424615

博客主要围绕Oracle上下合并两张表格展开，以查询所有教师和同学的name、sex和birthday为例，介绍基本思路是采用取别名和集合思想union，将教师与同学表格中需合并部分查找出来，修改别名使其相同后进行合并。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle 上下合并两张表格

题目：2查询所有教师和同学的name、sex和birthday.

在这里插入图片描述

查询需要的结果：

在这里插入图片描述

基本思路：取别名，与集合思想 union

将教师与同学的表格需要上下合并的查找出来，然后别名修改成相同的就可以改别名让他们相同，然后就可以合并了。

所需表格

--学生表
CREATE TABLE STUDENT
  (
  SNO       VARCHAR2 (3) NOT NULL,
 SNAME     VARCHAR2 (6) NOT NULL,
 SSEX      VARCHAR2 (3) NOT NULL,
 SBIRTHDAY DATE NOT NULL,
 CLASS     VARCHAR2 (5) NOT NULL
  );

COMMENT ON COLUMN STUDENT.SNO IS '学生编号';
COMMENT ON COLUMN STUDENT.SNAME IS '学生姓名';
COMMENT ON COLUMN STUDENT.SSEX IS '学生性别';
COMMENT ON COLUMN STUDENT.SBIRTHDAY IS '生日';
COMMENT ON COLUMN STUDENT.CLASS IS '班级';
--学生数据
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS)
VALUES ('108', '曾华', '男', TO_DATE ('1977-09-01', 'YYYY-MM-DD'), '95033');
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS)
VALUES ('105', '匡明', '男', TO_DATE ('1975-10-02', 'YYYY-MM-DD'), '95031');
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS)
VALUES ('107', '王丽', '女', TO_DATE ('1976-01-23', 'YYYY-MM-DD'), '95033');
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS)
VALUES ('101', '李军', '男', TO_DATE ('1976-02-20', 'YYYY-MM-DD'), '95033');
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS)
VALUES ('109', '王芳', '女', TO_DATE ('1975-02-10', 'YYYY-MM-DD'), '95031');
INSERT INTO STUDENT (SNO, SNAME, SSEX, SBIRTHDAY, CLASS)
VALUES ('103', '陆君', '男', TO_DATE ('1974-06-03', 'YYYY-MM-DD'), '95031');

--教师表
CREATE TABLE TEACHER
  (
  TNO       VARCHAR2 (3) NOT NULL,
 TNAME     VARCHAR2 (6) NOT NULL,
 TSEX      VARCHAR2 (3) NOT NULL,
 TBIRTHDAY DATE NOT NULL,
 PROF      VARCHAR2 (9) NOT NULL,
 DEPART    VARCHAR2 (15) NOT NULL,
  CONSTRAINT PK_TEACHER PRIMARY KEY (TNO)
  );

COMMENT ON COLUMN TEACHER.TNO IS '教师编号';
COMMENT ON COLUMN TEACHER.TNAME IS '教师姓名';
COMMENT ON COLUMN TEACHER.TSEX IS '性别';
COMMENT ON COLUMN TEACHER.TBIRTHDAY IS '生日';
COMMENT ON COLUMN TEACHER.PROF IS '职称';
COMMENT ON COLUMN TEACHER.DEPART IS '部门';

--教师数据
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES ('804', '李诚', '男', TO_DATE ('1958-12-02', 'YYYY-MM-DD'), '副教授', '计算机系');
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES ('856', '张旭', '男', TO_DATE ('1969-03-12', 'YYYY-MM-DD'), '讲师', '电子工程系');
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES ('825', '王萍', '女', TO_DATE ('1972-05-05', 'YYYY-MM-DD'), '助教', '计算机系');
INSERT INTO TEACHER (TNO, TNAME, TSEX, TBIRTHDAY, PROF, DEPART)
VALUES ('831', '刘冰', '女', TO_DATE ('1977-08-14', 'YYYY-MM-DD'), '助教', '电子工程系');

答案

--学生表
select sname name,ssex sex,sbirthday birthday,'学生' person from student 
--教师表
select tname name,tsex sex,tbirthday birthday,'老师' person from teacher
--学生表与教师表合并
select sname name,ssex sex,sbirthday birthday,'学生' person from student
union
select tname name,tsex sex,tbirthday birthday,'老师' person from teacher)
--合并后创建视图
create view vw_person as(
select sname name,ssex sex,sbirthday birthday,'学生' person from student
union
select tname name,tsex sex,tbirthday birthday,'老师' person from teacher)
order by person
查看
select * from vw_person