时间戳

首页 > 解决方案 > 通过比较两个表来识别缺失的组合

sql - 通过比较两个表来识别缺失的组合

问题描述

我的主表中有 3 列。

  1. 得分（0-99,100-110）
  2. 率 (5-9 ,10-15)
  3. 位置（A，B）

我拥有这些的所有组合（2 * 2 * 2 = 8 个组合）

以下是我的主表中的组合

score     rate   location
----------------------------
 0-99      5-9     A
100-110    5-9     A
 0-99     10-15    A
100-110   10-15    A
0-99       5-9     B
100-110    5-9     B
0-99      10-15    B
100-110   10-15    B

我有另一个包含实际数据的表。我想找出实际表格中所有缺失的组合。如何找到那些缺失的组合并附加到列中值为“0”的实际表中？

实际数据

score     rate   location  value 
---------------------------------
 0-99     10-15    A         3
100-110   10-15    A         6
0-99      10-15    B         1

预期产出

 score     rate   location  value 
------------------------------------
 0-99        5-9     A           0   
 0-99        10-15   A           3
100-110     10-15    A           6
100-110      5-9     B           0
0-99        10-15    B           1
100-110     5-9      A           0
100-110    10-15     B           0
0-99       10-15     B           0

标签： sqlpostgresqlgreenplum

解决方案

从您的实际数据和预期结果来看score，rate列location值似乎是固定的，因此您可以使用UNION ALLlet score, rate,location成为表。

CROSS JOIN为score, rate,location联合表生成笛卡尔积，制作完整表。

然后做OUTER JOIN

create table t(
  score varchar(50),
  rate varchar(50),
  location  varchar(50),
  value  int
);


insert into t values ('0-99','10-15','A',3);   
insert into t values ('100-110','10-15','A',6);
insert into t values ('0-99','10-15','B',1);

查询 1：

SELECT  
  s.score,
  r.rate,
  l.location,
  coalesce(t1.value,0)
FROM 
(
  SELECT '0-99' score
  UNION ALL
  SELECT '100-110'
) s
CROSS JOIN
(
  SELECT '10-15' rate
  UNION ALL
  SELECT '5-9'
) r
CROSS JOIN
(
  SELECT 'A' as "location"
  UNION ALL
  SELECT 'B'
) l
LEFT JOIN t t1 on s.score = t1.score and t1.rate = r.rate and t1.location = l.location
ORDER BY  l.location

结果：

|   score |  rate | location | coalesce |
|---------|-------|----------|----------|
|    0-99 | 10-15 |        A |        3 |
|    0-99 |   5-9 |        A |        0 |
| 100-110 | 10-15 |        A |        6 |
| 100-110 |   5-9 |        A |        0 |
| 100-110 |   5-9 |        B |        0 |
|    0-99 | 10-15 |        B |        1 |
| 100-110 | 10-15 |        B |        0 |
|    0-99 |   5-9 |        B |        0 |

推荐阅读