时间戳

首页 > 解决方案 > SQL 根据另一个表查找缺少列的行

sql - SQL 根据另一个表查找缺少列的行

问题描述

我有一个场景，我需要使用一个有效元素表来查找另一个表中缺少这些元素的记录，以便我可以反过来修补它们。这有点棘手，因为缺少的列数据位于“嵌套" 列（例如，它在另一列中有重复条目）。我觉得某种 LEFT OUTER JOIN 可能在这里起作用，但我不能完全理解。FWIW，我的基准是 Oracle 19c，但我最终还需要支持 Postgres 11+。问题总结如下：

给定一个租户表：

租户
1000
2000
3000

还有一个组表，其中每天对应一个新的组 id，每个组有一对多租户，其中每个租户对应一个子组 id：

日期 团体 租户 子组
2021-02-16 G1 1000 SG1
2021-02-16 G1 2000 SG2
2021-02-16 G1 3000 SG3
2021-02-17 G2 1000 SG4
2021-02-17 G2 2000 SG5
2021-02-18 G3 2000 SG6
2021-02-18 G3 3000 SG7
2021-02-19 G4 1000 SG8

查找缺少租户的组：

团体 租户
G2 3000
G3 1000
G4 2000
G4 3000

标签： sqlpostgresqloraclepostgresql-11oracle19c

解决方案

WITH
   tab1 AS
      ( SELECT TO_DATE('16.02.2021', 'DD.MM.YYYY') AS date_col, 'G1' AS group_col, 1000 AS tenant, 'SG1' AS subgroup FROM DUAL
        UNION ALL
        SELECT TO_DATE('16.02.2021', 'DD.MM.YYYY') AS date_col, 'G1' AS group_col, 2000 AS tenant, 'SG2' AS subgroup FROM DUAL
        UNION ALL
        SELECT TO_DATE('16.02.2021', 'DD.MM.YYYY') AS date_col, 'G1' AS group_col, 3000 AS tenant, 'SG3' AS subgroup FROM DUAL
        UNION ALL
        SELECT TO_DATE('17.02.2021', 'DD.MM.YYYY') AS date_col, 'G2' AS group_col, 1000 AS tenant, 'SG4' AS subgroup FROM DUAL
        UNION ALL
        SELECT TO_DATE('17.02.2021', 'DD.MM.YYYY') AS date_col, 'G2' AS group_col, 2000 AS tenant, 'SG5' AS subgroup FROM DUAL
        UNION ALL
        SELECT TO_DATE('18.02.2021', 'DD.MM.YYYY') AS date_col, 'G3' AS group_col, 2000 AS tenant, 'SG6' AS subgroup FROM DUAL
        UNION ALL
        SELECT TO_DATE('18.02.2021', 'DD.MM.YYYY') AS date_col, 'G3' AS group_col, 3000 AS tenant, 'SG7' AS subgroup FROM DUAL
        UNION ALL
        SELECT TO_DATE('19.02.2021', 'DD.MM.YYYY') AS date_col, 'G4' AS group_col, 1000 AS tenant, 'SG8' AS subgroup FROM DUAL
      ),
   tab2 AS
      ( SELECT 1000 AS tenant FROM DUAL
        UNION ALL
        SELECT 2000 AS tenant FROM DUAL
        UNION ALL
        SELECT 3000 AS tenant FROM DUAL
      )
SELECT *
  FROM ( SELECT t1.group_col,
                t2.tenant
           FROM ( SELECT DISTINCT group_col FROM tab1) t1
           CROSS JOIN tab2 t2
       ) x
 WHERE NOT EXISTS ( SELECT *
                      FROM tab1
                     WHERE tab1.group_col = x.group_col
                       AND tab1.tenant = x.tenant
                  )
  ORDER BY group_col,
           tenant;

结果：

GR     TENANT
-- ----------
G2       3000
G3       1000
G4       2000
G4       3000

该查询也适用于 Postgres（然后将其删除FROM DUAL，否则您将使其适应您的表）。

推荐阅读