【oracle进阶】01：with临时集cte,全外连接所有数据,NVL非null三元式,MINUS结果集

目录标题

复杂SQL查询：

使用WITH子句创建临时结果集
使用FULL OUTER JOIN找出两个表中所有记录
使用MINUS操作找出仅存在于一个表中的记录
使用UNION合并不同来源的差异
最后使用INNER JOIN将结果与源数据关联

1.CTE with临时结果集

CTE（Common Table Expression）是SQL中的一种临时结果集，它存在于单个SQL语句的执行范围内。CTE通过WITH子句定义，允许你创建一个命名的临时结果集，可以在后续的SELECT、INSERT、UPDATE、DELETE或其他CTE定义中引用。

CTE的语法结构

WITH cte_name (column1, column2, ...) AS (
    SELECT ...
)
SELECT * FROM cte_name;

CTE的主要特点

  1. 临时性：CTE只在包含它的SQL语句执行期间存在，执行完毕后自动消失
  2. 可读性：使代码更具结构性和可读性，特别是对于复杂查询
  3. 递归能力：可以创建递归查询，处理层次结构数据
  4. 复用性：同一个CTE可以在查询的多个部分被引用多次
  5. 模块化：可以将复杂查询分解为更小、更易管理的部分

CTE的使用场景

  1. 简化复杂查询：将多层嵌套的子查询分解为更易理解的部分
  2. 递归查询：处理树状或层次结构数据，如组织结构图、产品分类等
  3. 多次引用同一个子查询：避免重复编写同样的子查询
  4. 替代视图：当不需要永久存储视图时，CTE是一个很好的替代方案
  5. 窗口函数和聚合：结合窗口函数进行复杂计算

在你之前的SQL示例中

我使用CTE来定义两个临时结果集"差分元"和"差分先"：

WITH 差分元 AS (
    SELECT 
        番号,
        再リース回数,
        会計処理区分,
        履歴番号,
        未实现总额,
        '只在原始数据中存在' AS 数据状态
    FROM [1a.比較元データクエリ]
    MINUS
    SELECT 
        番号,
        再リース回数,
        会計処理区分,
        履歴番号,
        未实现总额,
        '只在原始数据中存在' AS 数据状态
    FROM [1b.比較先データクエリ]
),
差分先 AS (
    -- 第二个CTE定义
    ...
)
-- 主查询使用这两个CTE
SELECT ... FROM 差分元
UNION
SELECT ... FROM 差分先;

这使得查询更加模块化和可读，同时避免了在主查询中重复编写复杂的子查询逻辑。

一个简单的CTE示例

-- 使用CTE计算每个部门的平均薪资和全公司的平均薪资
WITH 部门平均薪资 AS (
    SELECT 部门ID, 部门名称, AVG(薪资) AS 平均薪资
    FROM 员工表
    GROUP BY 部门ID, 部门名称
),
全公司平均薪资 AS (
    SELECT AVG(薪资) AS 公司平均薪资
    FROM 员工表
)
SELECT 
    d.部门ID,
    d.部门名称,
    d.平均薪资,
    c.公司平均薪资,
    d.平均薪资 - c.公司平均薪资 AS 差异
FROM 部门平均薪资 d, 全公司平均薪资 c
ORDER BY 差异 DESC;

这个示例清晰地展示了如何使用CTE来结构化复杂查询，使其更易于理解和维护。

2.全外连接

在这个SQL查询中使用全外连接（FULL OUTER JOIN）是为了完整地比较两个数据源中的所有记录，不漏掉任何一条数据。全外连接有几个关键优势：

为什么使用全外连接（FULL OUTER JOIN）

  1. 完整性比较：全外连接可以找出两个数据源中的所有记录，包括：

    • 两边都有的匹配记录
    • 只在"元数据"中存在的记录
    • 只在"后数据"中存在的记录

  2. 数据差异分析：通过全外连接，你可以：

    • 发现哪些记录在一个数据源中存在而在另一个数据源中不存在
    • 对于两边都存在的记录，可以比较它们的"未实现总金额"值是否有差异

  3. 完整的数据审计：这种查询常用于数据迁移验证、系统升级前后的数据一致性检查、或定期数据质量审计

与其他连接方式相比

  • 内连接（INNER JOIN）：只会返回两边都匹配的记录，会丢失只存在于一边的数据
  • 左连接（LEFT JOIN）：只会返回"元数据"中的所有记录，可能会漏掉只存在于"后数据"中的记录
  • 右连接（RIGHT JOIN）：只会返回"后数据"中的所有记录，可能会漏掉只存在于"元数据"中的记录

实际应用场景

这种查询在以下场景特别有用：

  1. 数据验证：例如，验证数据从旧系统迁移到新系统后是否完整且正确
  2. 对账：比较两个时间点的账务数据，找出变化和差异
  3. 系统同步检查：检查两个系统间的数据是否正确同步
  4. 数据更新前后比较：在批量更新数据前，先进行比较以了解将发生的变化

在你的例子中，使用全外连接能够全面检查两组契约相关数据的差异，不管是记录的存在与否，还是具体的金额差异，都能一目了然地呈现出来。

好的，我会给你一个具体的数据例子，来展示这个全外连接查询的工作方式。

示例数据

元数据(原始数据)

番号 再リース回数 処理区分 履歴番号 未实现总金额
J001 0 A H100 500,000
J002 1 B H101 750,000
J003 0 A H102 1,200,000
J004 2 C H103 300,000
J005 0 B H104 650,000

后数据 (比较数据)

番号 再リース回数 処理区分 履歴番号 未实现总金额
J001 0 A H100 500,000
J003 0 A H102 1,250,000
J004 2 C H103 300,000
J006 1 A H105 800,000
J007 0 B H106 920,000

执行全外连接SQL后的结果

SELECT 
    NVL(元数据.番号, 后数据.番号) AS 番号,
    NVL(元数据.再リース回数, 后数据.再リース回数) AS 再リース回数,
    NVL(元数据.会計処理区分, 后数据.会計処理区分) AS 会計処理区分,
    NVL(元数据.履歴番号, 后数据.履歴番号) AS 履歴番号,
    元数据.未实现总额 AS 元_未实现总额,
    后数据.未实现总额 AS 先_未实现总额
FROM 
    元数据
FULL OUTER JOIN 
    后数据
ON 
    元数据.番号 = 后数据.番号
    AND 元数据.再リース回数 = 后数据.再リース回数
    AND 元数据.会計処理区分 = 后数据.会計処理区分
    AND 元数据.履歴番号 = 后数据.履歴番号

查询结果

番号 再リース回数 会計処理区分 履歴番号 元_未实现总额 先_未实现总额 说明
J001 0 A H100 500,000 500,000 两边都存在且金额相同
J002 1 B H101 750,000 NULL 只在元数据中存在
J003 0 A H102 1,200,000 1,250,000 两边都存在但金额不同
J004 2 C H103 300,000 300,000 两边都存在且金额相同
J005 0 B H104 650,000 NULL 只在元数据中存在
J006 1 A H105 NULL 800,000 只在后数据中存在
J007 0 B H106 NULL 920,000 只在后数据中存在

结果分析

  1. 完全匹配且金额相同的记录：J001和J004

    • 这些记录在两个数据源中完全一致

  2. 完全匹配但金额不同的记录：J003

    • 这条记录在两个数据源中的主键值相同，但未实现调整总金额不同
    • 元数据中是1,200,000，后数据中是1,250,000
    • 这可能表示有数据更新或差异

  3. 只在元数据中存在的记录：J002和J005

    • 这些记录可能在后数据中被删除，或尚未添加

  4. 只在后数据中存在的记录：J006和J007

    • 这些记录可能是新增的，或者在元数据中被删除

通过这个全外连接查询，你可以一眼看出两个数据源之间的所有差异，无论是记录的存在性还是字段值的差异。这对于数据验证、对账和一致性检查非常有用。

3、NVL函数基本语法

NVL(expr1, expr2)

参数解释：

  • expr1：要检查的表达式（可能包含NULL值）
  • expr2：如果expr1为NULL时要返回的值

NVL函数工作原理

NVL函数会检查第一个参数expr1：

  • 如果expr1不是NULL，则返回expr1的值
  • 如果expr1是NULL，则返回expr2的值

使用示例

基本示例

SELECT NVL(commission, 0) FROM employees;

这个查询会返回员工的commission值，如果commission为NULL，则返回0。

在计算中使用

SELECT employee_id, 
       salary, 
       commission_pct,
       NVL(commission_pct, 0) * salary AS commission_amount
FROM employees;

这个查询计算员工的佣金金额，如果commission_pct为NULL，则用0替代进行计算。

字符串类型的NULL处理

SELECT NVL(phone_number, '未提供') FROM employees;

这个查询会返回员工的电话号码，如果电话号码为NULL，则返回’未提供’。

注意事项

  1. expr1和expr2的数据类型应该相同或可以隐式转换
  2. 如果需要检查多个可能的值，可以考虑使用NVL2或COALESCE函数
  3. 在MySQL中，对应的函数是IFNULL
  4. 在SQL Server中，对应的函数是ISNULL
1的查询中 NVL函数的用法解释

在1的查询中，NVL函数的使用方式为：
sqlCopyNVL(比較元.字段名, 比較先.字段名)

这种用法的目的是：

如果"比較元"表中的值不为NULL，就使用"比較元"表中的值
如果"比較元"表中的值为NULL（可能是因为该记录在"比較元"中不存在），就使用"比較先"表中的值

这样做的目的是确保输出的字段值不为NULL（除非两个数据源中都是NULL）。
这种设计在FULL OUTER JOIN中特别有用，因为当某条记录只在一个表中存在时，另一个表中对应的字段值会是NULL。通过NVL函数，你可以确保始终获取到非NULL的值（除非两个数据源中都是NULL）。

查询结果分析
最终返回的结果集包括：

四个合并后的主键字段（使用NVL保证尽可能非NULL，除非两个数据源中都是NULL）
两个未实现调整总金额字段（分别来自两个表，未合并）

这种查询通常用于比较两组数据之间的差异，例如比较两个时间点的数据快照，或者比较预期值和实际值。

4. MINUS操作

先来看，假设一个数据库查询配置界面，分为三个部分：

  1. 左侧 [2a.差分抽出クエリ(元)] - 差分抽出查询(原始)：
    • 抽出项目：全项目
    • 抽出对象：[1a.元数据] 元数据
    • 抽出条件：无
    • 集计条件：无
    • 抽出顺序：无
    • MINUS操作
    • 抽出项目：全项目
    • 抽出对象：[1b.后数据] 后数据
    • 抽出条件：无
    • 集计条件：无
    • 抽出顺序：无
  2. 中间 [2b.差分抽出クエリ(先)] - 差分抽出查询(目标)：
    • 抽出项目：全项目
    • 抽出对象：[1b.后数据] 后数据
    • 抽出条件：无
    • 集计条件：无
    • 抽出顺序：无
    • MINUS操作
    • 抽出项目：全项目
    • 抽出对象：[1a.元数据] 元数据
    • 抽出条件：无
    • 集计条件：无
    • 抽出顺序：无
  3. 右侧 [2c.差分統合クエリ] - 差分统合查询：
    • 抽出项目：
      • 差分元.番号
      • 差分元.再リース回数
      • 差分元.会計処理区分
      • 差分元.履歴番号
    • 抽出对象：[2a.差分抽出クエリ(元)] 差分元
    • UNION操作
    • 抽出项目：
      • 差分先.番号
      • 差分先.再リース回数
      • 差分先.会計処理区分
      • 差分先.履歴番号
    • 抽出对象：[2b.差分抽出クエリ(先)] 差分先
    • 抽出条件：无
    • 集计条件：无
    • 抽出顺序：无

这整个配置描述了一个数据比较流程：
1. 首先，左侧查询获取只存在于"元数据"中而不存在于"后数据"中的记录
2. 然后，中间查询获取只存在于"后数据"中而不存在于"元数据"中的记录
3. 最后，右侧查询将这两部分结果合并在一起，形成完整的差异报告**
这与我们之前讨论的全外连接（FULL OUTER JOIN）查询有相似之处，但实现方式不同。这里使用了MINUS（差集）和UNION（并集）操作来实现数据比较，而不是直接使用FULL OUTER JOIN。

MINUS是SQL中的一个集合运算符，用于从第一个查询结果集中减去第二个查询结果集，只保留在第一个结果集中存在而在第二个结果集中不存在的行。

MINUS 语法

SELECT column1, column2, ... FROM table1
MINUS
SELECT column1, column2, ... FROM table2;

MINUS 的特点

  1. 集合操作：MINUS执行的是集合的差集操作
  2. 消除重复：MINUS自动消除结果中的重复行
  3. 列要求：两个查询必须有相同数量的列，且对应列的数据类型必须兼容
  4. 列名采用：结果集的列名采用第一个SELECT语句中的列名

数据库支持

  • Oracle：完全支持MINUS
  • PostgreSQL：使用EXCEPT代替MINUS（功能相同，语法不同）
  • SQL Server：使用EXCEPT代替MINUS
  • MySQL：不直接支持MINUS或EXCEPT，需要使用子查询和NOT IN/NOT EXISTS实现
  • SQLite：支持EXCEPT

实际示例

假设有两个表：

表1：原始员工表(Employees1)

ID Name Department
1 张三 销售
2 李四 财务
3 王五 IT
4 赵六 市场

表2：更新后员工表(Employees2)

ID Name Department
1 张三 销售
3 王五 IT
4 赵六 市场
5 钱七 HR

使用MINUS查询仅存在于原始表而不存在于更新表中的记录：

SELECT ID, Name, Department FROM Employees1
MINUS
SELECT ID, Name, Department FROM Employees2;

结果：

ID Name Department
2 李四 财务

同样，查询仅存在于更新表而不存在于原始表中的记录：

SELECT ID, Name, Department FROM Employees2
MINUS
SELECT ID, Name, Department FROM Employees1;

结果：

ID Name Department
5 钱七 HR

在MySQL中实现MINUS

由于MySQL不直接支持MINUS，可以使用LEFT JOIN和IS NULL实现类似功能：

SELECT e1.ID, e1.Name, e1.Department
FROM Employees1 e1
LEFT JOIN Employees2 e2 ON 
    e1.ID = e2.ID AND 
    e1.Name = e2.Name AND 
    e1.Department = e2.Department
WHERE e2.ID IS NULL;

在开始的案例中

在提供的查询中，MINUS用于找出只存在于"比較元"而不存在于"比較先"的记录，以及反之。这种操作对于数据比较、数据验证和变更跟踪非常有用。

5，使用INNER JOIN将结果与源数据关联

在已经有了差分统合结果的情况下，再与原来的总结果集进行INNER JOIN确实需要解释一下。

这种设计可能有以下几个原因：

  1. 获取完整信息：

    • 差分统合查询([2c.差分统合クエリ])可能只包含了关键的匹配字段，而没有包含未实现调整总金额等详细数据
    • 通过与比較元先結合クエリ进行JOIN，可以获取这些记录的完整信息，特别是两边的金额数据

  2. 筛选验证：

    • 这可能是一个验证步骤，确保差分统合中的记录确实在原始数据中存在
    • 有时候在复杂的数据处理流程中，这种重复验证可以防止因为中间处理步骤导致的数据异常

  3. 获取特定视图：

    • 差分結合中的记录可能是经过某种条件筛选的（例如只有金额差异大于某个阈值的记录）
    • 通过JOIN，可以获取这些特定记录的完整信息

  4. 业务流程要求：

    • 在某些审计或对账系统中，可能需要先生成差异报告，然后再针对差异记录进行详细分析
    • 这个JOIN操作可能是为后续的分析或报告准备数据

实际上，这种设计可能有点冗余，但在复杂的数据处理系统中，有时会优先考虑流程的清晰性和结果的可验证性，而不是查询的效率。在这个案例中，这个最终查询似乎是将关注点限定在那些已被确认有差异的记录上，同时保留了两边的未实现调整总金额，方便后续的差异分析。

这种模式在金融、审计系统中很常见，因为这些领域通常需要多层次的数据验证和清晰的处理流程。