db2 |左外部联接|避免重复

gupuwyp2 于 2022-11-07 发布在 DB2

关注(0)|答案(2)|浏览(181)

我有两个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个字符，请检查它是否完全匹配。
1.如果未找到匹配项，请删除一个字符，然后重试。
1.重复此步骤，直到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'条件造成的。让我们考虑以下示例。'expenses'表中'ID=1'的行与'environments'表中的两行匹配。因此，'ID=1'的行在结果中出现了两次。重要的是，'environments'表中的匹配行都具有相同的环境。因此，选择哪一行可以忽略不计。
如何避免给定方案中的重复值？
我已经尝试了以下解决方案：

db2

来源：https://stackoverflow.com/questions/73568662/db2-left-outer-join-avoid-duplicates

2条答案

按热度按时间

mitkmikd1#

试试看：

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

* /

| 识别码|费用|作业_E|作业环境|使用环境|
| - -|- -|- -|- -|- -|
| 一个|10个|AAAAAA| AAAAAAC|产品|
| 2个|五个|BBBBBB| BBBBBB|内部|
| 三个|三个|AAAAAAC| AAAAAAC|产品|
| 四个|七个|01年|01年|产品|
| 五个|第0页|第三十章|||

赞(0）回复(0）举报 2022-11-07

ee7vknir2#

除了被接受的答案，这是完美的工作，我提供了我最终得到的。

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

赞(0）回复(0）举报 2022-11-07

我来回答

db2 |左外部联接|避免重复

2条答案

相关问题

热门标签

最新问答