Oracle 12c按a%分组合并半空查询行的实现需求

现有数据环境 #

Oracle 12c环境下有一张数据表，结构包含Debit、Credit两列，数据如下：

已知该表不存在a%+a%和b%+b%格式的数据行。

查询需求 #

需要生成包含四列的查询结果：

• 前两列：包含a%且不含b%的Debit、Credit列对
• 后两列：包含a%且含b%的Debit、Credit列对

两组列对需按a%值关联匹配，将原查询返回的6行含空值结果按a%分组合并为4行，同组内任意排序、空值后置，预期结果如下：

原查询问题 #

原查询SQL返回6行含空值的结果，无法满足分组匹配的需求：

with t as (
  select 'a1' Debit, 'b1' Credit from dual
  union all select 'c1', 'a1' from dual
  union all select 'c2', 'a1' from dual
  union all select 'b2', 'a2' from dual
  union all select 'a2', 'b3' from dual
  union all select 'a2', 'c2' from dual)
select Debit, Credit, null DebitB, null CreditB
    from t
    where (Debit like 'a%' or Credit like 'a%')
    and (Debit not like 'b%' and Credit not like 'b%')
    union all
    select null, null, Debit, Credit
    from t
    where (Debit like 'a%' or Credit like 'a%')
    and (Debit like 'b%' or Credit like 'b%')

解决方案SQL #

通过提取每行的a%标识，对两类数据分别添加组内行号，再按标识和行号关联即可实现需求：

WITH t AS (
  SELECT 'a1' Debit, 'b1' Credit FROM dual
  UNION ALL SELECT 'c1', 'a1' FROM dual
  UNION ALL SELECT 'c2', 'a1' FROM dual
  UNION ALL SELECT 'b2', 'a2' FROM dual
  UNION ALL SELECT 'a2', 'b3' FROM dual
  UNION ALL SELECT 'a2', 'c2' FROM dual
),
data_a AS (
  SELECT 
    Debit,
    Credit,
    CASE 
      WHEN Debit LIKE 'a%' THEN Debit
      WHEN Credit LIKE 'a%' THEN Credit
    END AS a_id,
    ROW_NUMBER() OVER (PARTITION BY CASE 
                                      WHEN Debit LIKE 'a%' THEN Debit
                                      WHEN Credit LIKE 'a%' THEN Credit
                                    END ORDER BY Debit, Credit) AS rn
  FROM t
  WHERE (Debit LIKE 'a%' OR Credit LIKE 'a%')
    AND Debit NOT LIKE 'b%' AND Credit NOT LIKE 'b%'
),
data_b AS (
  SELECT 
    Debit AS DebitB,
    Credit AS CreditB,
    CASE 
      WHEN Debit LIKE 'a%' THEN Debit
      WHEN Credit LIKE 'a%' THEN Credit
    END AS a_id,
    ROW_NUMBER() OVER (PARTITION BY CASE 
                                      WHEN Debit LIKE 'a%' THEN Debit
                                      WHEN Credit LIKE 'a%' THEN Credit
                                    END ORDER BY Debit, Credit) AS rn
  FROM t
  WHERE (Debit LIKE 'a%' OR Credit LIKE 'a%')
    AND (Debit LIKE 'b%' OR Credit LIKE 'b%')
)
SELECT 
  da.Debit,
  da.Credit,
  db.DebitB,
  db.CreditB
FROM data_a da
FULL OUTER JOIN data_b db
  ON da.a_id = db.a_id AND da.rn = db.rn
ORDER BY da.a_id NULLS LAST, da.rn NULLS LAST;

逻辑说明 #

1. 提取a标识：用CASE语句从每行中提取a%格式的字段值作为分组标识a_id，确保两类数据能按相同的a值关联。
2. 添加组内行号：通过ROW_NUMBER()函数为每个a_id组内的数据生成行号，保证同组内的数据能按行号一一匹配。
3. 全连接匹配：使用FULL OUTER JOIN将两类数据按a_id和行号关联，自动补全空值，最终得到符合要求的4行结果。

内容的提问来源于stack exchange，提问作者Ayb