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列值的逻辑如下：

1. 如果Job的长度为8个字符，请检查它是否完全匹配
2. 如果找不到匹配项，请删除一个字符，然后重试
3. 重复此操作，直到Job为六个字符长

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

我尝试了以下查询

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"的行与"环境"表中的两行相匹配。因此，"ID＝1"的行在结果中出现两次。重要的是，"environments"表中的匹配行都具有相同的环境。因此，我选择哪一行可以忽略不计

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

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

• 如何加入第一行
• 从LEFT OUTER JOIN删除重复项
• LEFT OUTER JOIN如何返回比左表中存在的记录更多的记录

Answer 1

试试这个：

WITH 
expenses (ID, Expense, Job) AS
(
VALUES
(1, 10, 'AAAAAA')
, (2,  5, 'BBBBBB')
, (3,  3, 'AAAAAAC')
, (4,  7, 'AAAAAA01')
, (5,  0, 'XXX')
)
, environments (Job, Environment) AS 
(
VALUES
('AAAAAA01', 'PROD')
, ('BBBBBB'  , 'INT')
, ('AAAAAAC' , 'PROD')
)
-- The 1-st solution
SELECT
ID, Expense, Job_E
, Job_ENV, Environment
FROM
(
SELECT 
E.ID, E.Expense, E.Job AS Job_E
, ENV.Job AS Job_ENV, ENV.Environment
, ROW_NUMBER () OVER (PARTITION BY E.Job ORDER BY LENGTH (ENV.Job)) AS RN_
FROM expenses E
LEFT JOIN environments ENV ON ENV.JOB LIKE E.JOB || '%'
) T
WHERE RN_ = 1
ORDER BY ID
/*
-- Alternate solution
SELECT
E.ID, E.Expense, E.Job AS Job_E
, ENV.Job AS Job_ENV, ENV.Environment
FROM expenses E
LEFT JOIN TABLE
(
SELECT ENV.Job, ENV.Environment
FROM environments ENV 
WHERE ENV.JOB LIKE E.JOB || '%'
ORDER BY LENGTH (ENV.Job)
FETCH FIRST 1 ROW ONLY
) ENV ON 1 = 1
ORDER BY E.ID
*/

>>环境AAAAAABBBBBINTAAAAAACAAAAAA01

ID	支出	JOB_E	JOB_ENV
1	10		AAAAAA C	PROD
2	5	BBBB
3	3	AAAAAA C	PROD
4	7	AAAAAA 01	PROD
5	0	XXX

除了能完美工作的公认答案外，我还提供了我最终得到的答案。

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表中选择不同的值。