Группировка по максимальной дате для записи клиента (несколько таблиц)

Всем доброго утра!

У нас есть таблица, показывающая клиентов, их склады и дату последнего изменения их записи. Моя задача найти самый последний номер клиента для конкретного склада. Большинство из них не изменились, но есть довольно много, которые изменились. Я создал приведенный ниже запрос, который отлично работает, чтобы найти дату последнего изменения для всех складов в моей таблице.

Select
  WHS,
  Max(AMENDT) As MAX_AMENDT
From
  MBC6REVQ
Group By
  WHS
Order By
  WHS,
  Max(Distinct (Select
    Max(MAX_AMENDT)
  From
    MBC6REVQ
  Group By
    MBC6REVQ.WHS
  Fetch First
    1 Rows Only)) Desc

У меня проблема в том, что теперь мне нужно добавить номер клиента и присоединиться к моей таблице клиентов, чтобы получить имя клиента. Простое добавление поля CUSNO приводит к ошибке, поэтому мне нужно добавить CUSNO в «группировать по». Это дает мне несколько записей для каждого склада. Мои данные выглядят так:

    WHS   AMENDT
    A01   1150101
    A01   1130704
    A02   1141030
    A07   1071101
    A10   1020905

.. поэтому запрос правильно возвращает:

    WHS   AMENDT
    A01   1150101
    A02   1141030
    A07   1071101
    A10   1020905

Мне нужно добавить столбец CUSNO, потому что номер клиента изменился. Тогда мой запрос выглядит так:

Select
  WHS,
  CUSNO,
  Max(AMENDT) As MAX_AMENDT
From
  MBC6REVQ
Group By
  WHS, CUSNO
Order By
  WHS,
  Max(Distinct (Select
    Max(AMENDT)
  From
    MBC6REVQ
  Group By
    MBC6REVQ.WHS
  Fetch First
    1 Rows Only)) Desc

... и возвращает:

    WHS   CUSNO   AMENDT
    A01   1003    1150101
    A01   1056    1130704
    A02   1011    1141030
    A07   1169    1071101
    A10   1012    1020905

... когда мне нужно иметь:

    WHS   CUSNO   AMENDT
    A01   1003    1150101
    A02   1011    1141030
    A07   1169    1071101
    A10   1012    1020905

Кто-нибудь может помочь? Это сводит меня с ума!!! Любая помощь приветствуется!

заранее спасибо

Мэтт


sql greatest-n-per-group db2-400 ibm-midrange
person user3593083    schedule 19.01.2016    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Я не знаю всех возможностей db2400, поэтому могут быть более простые/лучшие способы справиться с этим, если, например, он поддерживает оконные функции. Тем не менее, это самый «общий» SQL, о котором я могу думать, поэтому он, скорее всего, будет работать.

SELECT
    T1.WHS,
    T1.CUSNO,
    T1.AMENDT
FROM
    MBC6REVQ T1
LEFT OUTER JOIN MBC6REVQ T2 ON
    T2.WHS = T1.WHS AND
    T2.AMENDT > T1.AMENDT
WHERE
    T2.WHS IS NULL

В качестве альтернативы, если db2400 поддерживает NOT EXISTS и коррелированные подзапросы:

SELECT
    T1.WHS,
    T1.CUSNO,
    T1.AMENDT
FROM
    MBC6REVQ T1
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM
            MBC6REVQ T2
        WHERE
            T2.WHS = T1.WHS AND
            T2.AMENDT > T1.AMENDT
    )

В любом случае вам нужно также решить, как вы хотите обрабатывать связи, и соответствующим образом кодировать для этого.

person Tom H    schedule 19.01.2016
comment
Спасибо, Том! Я никогда не думал об использовании T2.AMENDT › T1.AMENDT. Это работает отлично!!!!!! - person user3593083; 19.01.2016
comment
DB2 для i поддерживает НЕ СУЩЕСТВУЮЩИЕ и коррелированные подзапросы... более старая DB2/400 тоже... по крайней мере, с тех пор, как я начал использовать ее в эпоху IIRC v3r6. - person Charles; 19.01.2016
comment
DB2 поддерживает оконные функции, но i не поддерживает (последнее, что я знал) использование агрегатов, а также только подмножество обычных оконных функций. Тем не менее он имеет номер ранга/строки... - person Clockwork-Muse; 23.01.2016