Oracle 数据库中，UNION ALL创建视图的使用详解

目录

UNION ALL 的特点

UNION ALL 的作用 

1. 合并结果集

2. 保留重复行

3. 提高性能

UNION ALL 的使用场景

1. 日志或数据拼接

2. 区分数据来源

3. 解决分区表查询

注意事项

 在创建视图中的作用

场景 1：合并多个表的数据到视图

表结构

目标

SQL 实现

解析

场景 2：合并不同类型的数据

表结构

目标

解析

场景 3：合并分区数据

表结构

目标

SQL 实现

场景 4：跨业务系统的数据合并

表结构

目标

SQL 实现

解析

场景 5：多条件动态数据组合

表结构

目标

SQL 实现

解析

总结

在 Oracle 数据库中，UNION ALL 是一种用于将多个查询的结果合并为一个结果集的 SQL 运算符。它的功能是将两个或多个查询的结果 直接合并，包括重复的行。以下是对 UNION ALL 的详细解释

SELECT 列1, 列2, ...
FROM 表1
[WHERE 条件]
UNION ALL
SELECT 列1, 列2, ...
FROM 表2
[WHERE 条件]

UNION ALL 的特点

  • 不去重

    • 与 UNION 不同，UNION ALL 不会去除结果集中的重复行，因此效率更高。
    • 如果需要去重，请使用 UNION。

  • 支持列数和数据类型匹配

    • 合并的各个查询必须有 相同数量的列，且每一列的数据类型必须兼容。
    • 列的名称可以不同，但顺序和类型必须一致。

  • 执行顺序

    • UNION ALL 以查询的书写顺序逐行合并结果，查询的顺序会直接影响最终的结果。

UNION ALL 的作用 

1. 合并结果集

UNION ALL 适合在明确不需要去重的情况下合并多个查询结果，例如：

SELECT 'Apple' AS Fruit FROM DUAL
UNION ALL
SELECT 'Banana' AS Fruit FROM DUAL;

结果：

Fruit
-----
Apple
Banana

2. 保留重复行

如果数据中存在重复行，并且需要完整保留（包括重复行），可以使用 UNION ALL：

SELECT 'Apple' AS Fruit FROM DUAL
UNION ALL
SELECT 'Apple' AS Fruit FROM DUAL;

结果：

Fruit
-----
Apple
Apple

与 UNION 对比：

SELECT 'Apple' AS Fruit FROM DUAL
UNION
SELECT 'Apple' AS Fruit FROM DUAL;

结果:

Fruit
-----
Apple

3. 提高性能

由于 UNION ALL 不需要去重，因此在处理大量数据时比 UNION 性能更高。

SELECT employee_id, salary FROM employees
WHERE department_id = 10
UNION ALL
SELECT employee_id, salary FROM employees
WHERE department_id = 20;

UNION ALL 的使用场景

1. 日志或数据拼接

将多张日志表的记录合并为一个结果集：

SELECT log_id, log_date, log_message FROM log_table_2023
UNION ALL
SELECT log_id, log_date, log_message FROM log_table_2024;

2. 区分数据来源

通过额外的列标识数据来源：

SELECT '2023年数据' AS 来源, log_id, log_message
FROM log_table_2023
UNION ALL
SELECT '2024年数据' AS 来源, log_id, log_message
FROM log_table_2024;

3. 解决分区表查询

分区表的分区查询结果可以用 UNION ALL 合并：

SELECT * FROM sales PARTITION (Q1)
UNION ALL
SELECT * FROM sales PARTITION (Q2);

注意事项

  1. 结果集大小
    如果使用 UNION ALL 合并了大量重复行，可能会导致结果集变得非常庞大，占用更多的内存和存储。

  2. 列的兼容性
    合并的列需要有相同或兼容的数据类型，例如数值和字符类型不能直接混合。

  3. 排序需求
    如果需要对最终结果排序，应在最后的结果集使用 ORDER BY：

SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2
ORDER BY column1;

 性能对比：UNION vs UNION ALL

特性UNIONUNION ALL
是否去重是否
执行速度较慢（去重耗时）较快（直接合并）
内存占用较高（需要排序去重）较低（不需要排序）
结果是否包含重复行否是

 在创建视图中的作用

在 Oracle SQL 中，UNION ALL 可以在创建复杂视图时起到整合多个数据源的关键作用。通过合并多个查询结果并保留重复数据，UNION ALL 非常适用于需要从多张表、多个分区或不同来源数据中创建整合视图的场景。

以下是一些详细的举例和解析：

场景 1：合并多个表的数据到视图

假设有多个分区表存储了不同年份的销售数据，现在需要创建一个视图，将所有年份的销售数据整合为一个统一的视图。

表结构

每年的销售数据分别存储在以下分区表中：

  • sales_2022 (字段: sale_id, product_id, amount, sale_date)
  • sales_2023 (字段: sale_id, product_id, amount, sale_date)
目标

创建一个视图，能够统一访问所有年的销售数据。

SQL 实现
CREATE OR REPLACE VIEW all_sales AS
SELECT sale_id, product_id, amount, sale_date, '2022年' AS year_label
FROM sales_2022
UNION ALL
SELECT sale_id, product_id, amount, sale_date, '2023年' AS year_label
FROM sales_2023;

查询视图数据

SELECT * FROM all_sales WHERE product_id = 101;
解析
  1. 添加来源标识：
    • 使用 '2022年' AS year_label 和 '2023年' AS year_label 区分数据来源。
  2. 不去重：
    • 因为不同年份的数据不重叠，无需去重，UNION ALL 比 UNION 更高效。
  3. 动态扩展：
    • 可以通过追加新的 SELECT 块来合并后续年份的数据，而不影响现有视图。

场景 2：合并不同类型的数据

假设公司有两种类型的员工记录：

  1. 正式员工存储在表 full_time_employees 中。
  2. 合同工存储在表 contract_employees 中。
表结构
  • full_time_employees (字段: employee_id, name, salary, hire_date, job_type 固定为 '正式员工')
  • contract_employees (字段: employee_id, name, salary, hire_date, job_type 固定为 '合同工')
目标

创建一个统一的视图，显示所有员工的数据，包括其身份类型。

SQL 实现

CREATE OR REPLACE VIEW all_employees AS
SELECT employee_id, name, salary, hire_date, '正式员工' AS job_type
FROM full_time_employees
UNION ALL
SELECT employee_id, name, salary, hire_date, '合同工' AS job_type
FROM contract_employees;

查询视图数据

SELECT * FROM all_employees WHERE salary > 5000 ORDER BY hire_date;
解析
  1. 不同来源的数据整合：
    • 数据源表结构类似，UNION ALL 能轻松整合不同表的数据。
  2. 字段类型兼容：
    • 列数和数据类型需一致，例如两个表中的 salary 均为数值类型。
  3. 性能优越：
    • 使用 UNION ALL 避免去重操作，显著提升查询速度。

场景 3：合并分区数据

假设有一个大型分区表 sales 按季度存储数据（PARTITION BY），需要创建视图合并其中的 Q1 和 Q2 数据。

表结构
  • 分区表 sales (字段: sale_id, region, amount, sale_date)
    • 分区：Q1、Q2、Q3、Q4
目标

创建视图只合并第一季度和第二季度的数据。

SQL 实现
CREATE OR REPLACE VIEW sales_h1 AS
SELECT * FROM sales PARTITION (Q1)
UNION ALL
SELECT * FROM sales PARTITION (Q2);

 查询视图数据

SELECT region, SUM(amount) AS total_sales
FROM sales_h1
GROUP BY region;

 解析

  1. 分区查询的整合：
    • 使用 UNION ALL 按分区提取数据，避免全表扫描。
  2. 优化性能：
    • 分区表与 UNION ALL 配合能够高效处理特定数据的整合。

场景 4：跨业务系统的数据合并

假设一家企业的 HR 和财务系统分别存储员工的基本信息和薪资信息，现在需要创建一个视图整合这两部分数据。

表结构
  • HR 系统：hr_employees (字段: employee_id, name, department, position)
  • 财务系统：finance_employees (字段: employee_id, name, salary, pay_date)
目标

创建视图合并员工的所有信息，保留重复行以便后续分析。

SQL 实现
CREATE OR REPLACE VIEW unified_employees AS
SELECT employee_id, name, department, position, NULL AS salary, NULL AS pay_date
FROM hr_employees
UNION ALL
SELECT employee_id, name, NULL AS department, NULL AS position, salary, pay_date
FROM finance_employees;

查询视图数据 

SELECT employee_id, name, MAX(salary) AS max_salary
FROM unified_employees
WHERE name IS NOT NULL
GROUP BY employee_id, name;
解析
  1. 字段对齐：
    • 两个表字段不完全一致，使用 NULL 补齐不存在的列。
  2. 数据整合：
    • 利用 UNION ALL 合并不同系统中的数据，方便后续在统一视图中分析。

场景 5：多条件动态数据组合

假设需要将客户的订单数据按照地区和时间分别进行合并，提供给不同的业务部门使用。

表结构
  • orders (字段: order_id, customer_id, region, order_date, amount)
目标

创建视图，分别合并北方和南方地区的数据，并按年份标识来源。

SQL 实现
CREATE OR REPLACE VIEW regional_orders AS
SELECT order_id, customer_id, region, order_date, amount, '北方' AS region_label
FROM orders
WHERE region IN ('北方')
UNION ALL
SELECT order_id, customer_id, region, order_date, amount, '南方' AS region_label
FROM orders
WHERE region IN ('南方');

查询视图数据

SELECT region_label, SUM(amount) AS total_sales
FROM regional_orders
WHERE order_date >= TO_DATE('2024-01-01', 'YYYY-MM-DD')
GROUP BY region_label;
解析
  1. 动态整合：
    • 按条件动态分区和标记数据来源，方便后续业务使用。
  2. 聚合分析：
    • 在视图基础上进行统计分析，减少重复查询复杂性。

总结

在复杂视图的创建中，UNION ALL 的作用可以总结为以下几点：

  1. 高效整合多数据源：适合合并大规模数据且无需去重的场景。
  2. 动态扩展性：轻松添加新的查询来源而不破坏视图。
  3. 保留数据完整性：不会丢失重复行，适合需要完整记录的分析场景。
  4. 优化性能：避免去重操作，处理速度快，特别是对大表或分区表的整合。

在设计复杂视图时，根据实际业务需求选择是否使用 UNION ALL，结合字段对齐、动态标识等方式，使视图更灵活高效！

UNION ALL 是 Oracle SQL 中非常实用的工具，尤其在以下情况下效果最佳：

  • 不需要去重时。
  • 数据量较大且对性能要求高时。
  • 需要保留重复数据时。

如需去重，请改用 UNION。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/940139.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

OpenEuler 22.03 安装 flink-1.17.2 集群

OpenEuler 22.03 安装 flink-1.17.2 集群

零：规划 本次计划安装三台OpenEuler 22.03 版本操作系统的服务器，用于搭建 flink 集群。这里使用flink1.17.2 的原因，是便于后续与springboot的整合 服务器名IP地址作用其他应用flink01192.168.159.133主jdk11、flink-1.17.2flink02192.168.…
阅读更多...
[数据结构] 链表

[数据结构] 链表

目录 1.链表的基本概念 2.链表的实现 -- 节点的构造和链接 节点如何构造? 如何将链表关联起来? 3.链表的方法(功能) 1).display() -- 链表的遍历 2).size() -- 求链表的长度 3).addFirst(int val) -- 头插法 4).addLast(int val) -- 尾插法 5).addIndex -- 在任意位置…
阅读更多...
20241220在荣品开发板PRO-RK3566的buildroot下适配gc2093

20241220在荣品开发板PRO-RK3566的buildroot下适配gc2093

20241220在荣品开发板PRO-RK3566的buildroot下适配gc2093 2024/12/20 16:00 余顺?PRO-RK3566开发板 挂 gc2093模块。刷 buildroot的预编译固件。 update-pro-rk3566-buildroot-hdmi-20231130-034633.img 1、现在发现 qcamera的 拍照Capture、Record录像模式都是640x480分辨率…
阅读更多...
实习冲刺数据库练习-01 基础查询

实习冲刺数据库练习-01 基础查询

原题链接：牛客网在线编程_SQL篇_非技术快速入门 数据表示例： 根据数据表示例要求我们完成以下查询： （1）获取用户信息表中所有的数据，请你取出相应结果 （2）获取用户的设备id对应的…
阅读更多...
【Mars3d】设置backgroundImage、map.scene.skyBox、backgroundImage来回切换

【Mars3d】设置backgroundImage、map.scene.skyBox、backgroundImage来回切换

相关链接： http://mars3d.cn/editor-vue.html?keyex_1_2_1&idmap/other/backgroundImg 实现代码： export function show1() {map.setOptions({scene: {backgroundType: "image",backgroundImage: "url(//data.mars3d.cn/img/busin…
阅读更多...
telnet命令检查端口

telnet命令检查端口

1、简介 telnet是一种用于远程登录的协议，可以通过telnet客户端连接到远程主机，并在远程主机上执行命令。 2、使用telnet命令检查端口 2.1 进入linux终端 2.2 输入telnet命令 如果没有安装telnet命令，请执行以下命令安装 sudo yum install…
阅读更多...
Unity 根据文本宽度自动移动图像位置

Unity 根据文本宽度自动移动图像位置

游戏中有时候需要变动的显示一个物品的数量，变化的文本宽度不停的变化，这时候需要将物品的icon随着文本的长度而改变位置。 实现思路：使用Content Size Fitter来动态改变内容的大小。 首先建立一个文本组件，添加Content Size Fi…
阅读更多...
基于Springboot人口老龄化社区服务与管理平台【附源码】

基于Springboot人口老龄化社区服务与管理平台【附源码】

基于Springboot人口老龄化社区服务与管理平台 效果如下： 系统登陆页面 系统主页面 社区信息页面 社区文件页面 活动报名页面 走访任务管理页面 社区资讯页面 老人信息管理页面 研究背景 随着社会老龄化的加剧，老年人口比例逐渐增加，对老年…
阅读更多...
加密数据库在现代企业中的应用实践

加密数据库在现代企业中的应用实践

以下是对加密数据库在现代企业中的应用实践的详细阐述： 一、加密数据库的应用背景 随着信息技术的飞速发展，现代企业对于数据的安全性和隐私保护要求越来越高。数据库作为存储大量敏感信息的关键设施，其安全性直接关系到企业的商业利益和声誉…
阅读更多...
安卓环境配置及打开新项目教程，2024年12月20日最新版

安卓环境配置及打开新项目教程，2024年12月20日最新版

1.去官网下载最新的Android Studio，网址：https://developer.android.com/studio?hlzh-cn 2.下载加速器，注册账号，开启加速器。网址：放在文末。 3.下载安卓代码，项目的路径上不能有中文，特别是…
阅读更多...
20241217使用M6000显卡在WIN10下跑whisper来识别中英文字幕

20241217使用M6000显卡在WIN10下跑whisper来识别中英文字幕

20241217使用M6000显卡在WIN10下跑whisper来识别中英文字幕 2024/12/17 17:21 缘起，最近需要识别法国电影《地下铁》的法语字幕，使用 字幕小工具V1.2【whisper套壳/GUI封装了】 无效。 那就是直接使用最原始的whisper来干了。 当你重装WIN10的时候&#…
阅读更多...
sqlite3 支持位运算 和view和 triger

sqlite3 支持位运算 和view和 triger

数据设置条件以后可以.根据门限自动调整其他的值 由数据库记录修改时间,及记录-> 网元设备的告警产生时间,设置超时清除时间,记录系统的原始时间戳 CPp 有 sqlite 支持 json 导出字符串,json 库将字符串,映射为结构体 triger update table 更新到一个 可设置参数列表 ,view …
阅读更多...
11-C语言结构体（下篇）

11-C语言结构体（下篇）

一、结构体指针变量 结构体指针变量：本质上是一个指针变量，保存的是结构体变量的地址。 1.结构体变量的地址 结构体变量的地址：对结构体变量名取地址。 代码演示 typedef struct stu {char name[32];int age;float score; }STU;int main…
阅读更多...
linux普通用户使用sudo不需要输密码

linux普通用户使用sudo不需要输密码

1.root用户如果没有密码，先给root用户设置密码 sudo passwd root #设置密码 2.修改visudo配置 su #切换到root用户下 sudo visudo #修改visudo配置文件 用户名 ALL(ALL) NOPASSWD: ALL #下图所示处新增一行配置 用户名需要输入自己当前主机的用户名
阅读更多...
百度面试手撕 go context channel部分学习

百度面试手撕 go context channel部分学习

题目 手撕 对无序的切片查询指定数 使用context进行子协程的销毁 并且进行超时处理。 全局变量定义 var (startLoc int64(0) // --- 未处理切片数据起始位置endLoc int64(0) // --- 切片数据右边界 避免越界offset int64(0) // --- 根据切片和协程数量 在主线程 动态设…
阅读更多...
任务一登录安全加固

任务一登录安全加固

1 （1）、（2） secpol.msc打开本地安全策略 2 （1） DCOM: 在安全描述符定义语言(SDDL)语法中的计算机访问限制 没有定义 DCOM: 在安全描述符定义语言(SDDL)语法中的计算机启动限制 没有定义 Microsoft 网络服…
阅读更多...
无人机推流直播平台EasyDSS视频技术如何助力冬季森林防火

无人机推流直播平台EasyDSS视频技术如何助力冬季森林防火

冬季天干物燥，大风天气频繁，是森林火灾的高发期。相比传统的人力巡查，无人机具有更高的灵敏度和准确性，尤其在夜间或浓雾天气中，依然能有效地监测潜在火源。 无人机可以提供高空视角和实时图像传输，帮助巡…
阅读更多...
写SQL太麻烦？免费搭建 Text2SQL 应用，智能写 SQL | OceanBase AI 实践

写SQL太麻烦？免费搭建 Text2SQL 应用，智能写 SQL | OceanBase AI 实践

自OceanBase 4.3.3版本推出以来，向量检索的能力受到了很多客户的关注，也纷纷表达希望OB能拓展更多 多模数据库大模型 的AI应用实践。 在上篇文章 👉 OceanBase LLM，免费构建你的专属 AI 助手 ，我们介绍了如何去搭建一…
阅读更多...
Halcon 机器视觉案例 之 药剂液面高度测量

Halcon 机器视觉案例 之 药剂液面高度测量

第二篇 机器视觉案例 之 药剂液面高度测量 文章目录 第二篇 机器视觉案例 之 药剂液面高度测量1.案例要求2.实现思路2.1获得液面的位置：2.1.1 获得每支药剂的位置坐标2.1.2 根据药剂的横坐标设置卡尺工具助手找到每一个液面的位置 2.2 获得基准线的位置：…
阅读更多...
使用k6进行MongoDB负载测试

使用k6进行MongoDB负载测试

1.安装环境 安装xk6-mongo扩展 ./xk6 build --with github.com/itsparser/xk6-mongo 2.安装MongoDB 参考Docker安装MongoDB服务-CSDN博客 连接成功后新建test数据库和sample集合 3.编写脚本 test_mongo.js import xk6_mongo from k6/x/mongo;const client xk6_mongo.new…
阅读更多...
最新文章

Copyright @ 2022~2023