数据仓库服务 GAUSSDB(DWS)-UNION操作符的使用:示例

时间：2024-01-19 16:49:12

数据仓库服务 GAUSSDB(DWS) 数据读取

示例

创建学生信息表student（ID、姓名、性别、学校）。

SET current_schema=public;
DROP TABLE IF EXISTS student;
CREATE table student( 
sId VARCHAR(10) NOT NULL  ,
sname VARCHAR(10) NOT NULL  ,
ssex VARCHAR(10) NOT NULl  ,
sschool VARCHAR(10) NOT NULl  );

给表student插入数据。

INSERT INTO student VALUES('s01' , '赵雷' , '男', 'NENU'); 
INSERT INTO student VALUES('s02' , '钱电' , '男', 'SJTU'); 
INSERT INTO student VALUES('s03' , '孙风' , '男', 'Tongji'); 
INSERT INTO student VALUES('s04' , '李云' , '男', 'CCOM'); 
INSERT INTO student VALUES('s05' , '周梅' , '女', 'FuDan'); 
INSERT INTO student VALUES('s06' , '吴兰' , '女', 'WHU'); 
INSERT INTO student VALUES('s07' , '郑竹' , '女', 'NWAFU'); 
INSERT INTO student VALUES('s08' , '张三' , '女', 'Tongji');

查看表student。
1

SELECT * FROM student;
回显如下：

创建教师信息表teacher（ID、姓名、性别、学校）。

DROP TABLE IF EXISTS teacher;
CREATE table teacher( 
tid VARCHAR(10) NOT NULL  ,
tname VARCHAR(10) NOT NULL  ,
tsex VARCHAR(10) NOT NULL  ,
tschool VARCHAR(10) NOT NULL  );

给表teacher插入数据。

1
2
3

INSERT INTO teacher VALUES('t01' , '张磊', '男', 'FuDan'); 
INSERT INTO teacher VALUES('t02' , '李强', '男', 'WHU'); 
INSERT INTO teacher VALUES('t03' , '王刚', '男', 'Tongji');

查询表teacher。
1

SELECT * FROM teacher;

使用UNION（合并且去重）获取学生和教师所在学校，并按学校名称首字母升序排序。

SELECT t.school  FROM (
     SELECT sschool AS school
      FROM student
      UNION
      SELECT tschool AS school
      FROM teacher
  ) t
  ORDER BY t.school ASC;

回显如下：

使用UNION ALL（合并不去重）获取所有学生和教师所在学校，并按学校名称首字母升序排序。

SELECT t.school  FROM (
     SELECT sschool AS school
      FROM student
      UNION ALL
      SELECT tschool AS school
      FROM teacher
  ) t
  ORDER BY t.school ASC;

使用UNION ALL（合并带有WHERE子句SQL结果集）获取来自'Tongji'的学生和教师的所有信息，并按学生和教师的编号升序排序。

SELECT t.*  FROM  (
  SELECT Sid AS id ,Sname AS name  ,Ssex AS sex  ,Sschool AS school
  FROM student
  WHERE Sschool='Tongji'
  UNION ALL
  SELECT Tid AS id ,Tname AS name  ,Tsex AS sex  ,Tschool AS school
  FROM teacher
  WHERE Tschool='Tongji'
) t
  ORDER BY t.id ASC;