Oracle 上下合并两张表格

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

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！