DB2 _~_

Question

我有两个DB2表，expenses和environments，我需要加入它们。

expenses

ID    Expense  Job
1     10       AAAAAA
2     5        BBBBBB 
3     3        AAAAAAC
4     7        AAAAAA01

environments

Job        Environment
AAAAAA01   PROD
BBBBBB     INT
AAAAAAC    PROD

现在，我希望将environments表加入到expenses表中，以便知道费用发生在哪种环境中。这些表的问题是，两个表的Job列略有不同。因此，我不能仅仅加入ON ENVIRONMENTS.JOB = EXPENSES.JOB --值长在6到8个字符之间，而且expenses表中的值通常比environments表中的值短。

比较Job列值的逻辑如下所示：

如果again.

• Repeat的长度为8个字符，则

1. 检查与Job的准确匹配；如果没有找到匹配的字符，则检查
2. ，删除一个字符，然后尝试Job，直到Job有6个字符。

在每个步骤中，如果找到多个值，请选择第一个匹配项。

我尝试了以下查询

SELECT E.expense, ENV.environment
FROM EXPENSES E
LEFT OUTER JOIN ENVIRONMENTS ENV
ON LEFT(ENV.JOB, 6) = LEFT(E.JOB, 6)
GROUP BY E.expense, ENV.environment

问题是我在结果中接收到重复的值。因此，在按环境分组时，我得到的费用比原来的要多。

实际产出：

ID    Expense  Job        Job         Environment
1     10       AAAAAA     AAAAAA01    PROD
1     10       AAAAAA     AAAAAAC     PROD
2     5        BBBBBB     BBBBBB      INT
3     3        AAAAAAC    AAAAAA01    PROD
3     3        AAAAAAC    AAAAAAC     PROD
4     7        AAAAAA01   AAAAAA01    PROD
4     7        AAAAAA01   AAAAAAC     PROD

期望产出：

ID    Expense  Job        Job         Environment
1     10       AAAAAA     AAAAAA01    PROD
2     5        BBBBBB     BBBBBB      INT
3     3        AAAAAAC    AAAAAA01    PROD
4     7        AAAAAA01   AAAAAA01    PROD

第二个Job列中的值可以是AAAAAA01或AAAAAAC。这完全不相关。

​

这是由于ON条件造成的。让我们考虑下面的例子。来自ID=1表的带有expenses的行匹配environments表中的两行。因此，带有ID=1的行在结果中发生两次。重要的是，environments表中的匹配行都具有相同的环境。因此，我选择的行可以忽略不计。

如何避免给定场景中的重复值？

我已经尝试了以下解决方案：

• How to Join to first row
• Remove Duplicates from LEFT OUTER JOIN
• How can a LEFT OUTER JOIN return more records than exist in the left table?

Answer 1

除了公认的答案外，我还提供了我最终得到的答案，这个答案非常有效。

SELECT E.expense,
       ENV.environment
FROM   expenses E
       LEFT JOIN (SELECT DISTINCT LEFT(job, 6) AS job,
                                  environment
                  FROM   environments) ENV
              ON LEFT(ENV.job, 6) = LEFT(E.job, 6)

然后，还可以使用此查询按日期(例如，年份和月份)和环境对费用进行分组。诀窍是只从environments表中选择不同的值。