Блог

Главная - Вопросы по программированию - Используйте вычисленное значение столбца для поиска определенного значения из той же исходной таблицы

Используйте вычисленное значение столбца для поиска определенного значения из той же исходной таблицы

#sql #sql-server

#sql #sql-сервер

Вопрос:

У меня есть таблица в SQL Server, ShippingDocSummary которая содержит все исторические записи для отгрузочных документов. Для заданного номера документа может быть любое количество исторических записей / транзакций. То, что я пытаюсь сделать, это создать представление, которое суммирует эту таблицу и вычисляет различные значения столбцов. Моя проблема возникает при попытке получить определенное значение из исходной таблицы на основе вычисленного значения столбца из той же исходной таблицы.

Вот упрощенный пример того, как выглядит исходная таблица:

 DocNum    DocHistID    StatusCode   StatusQty    StatusDate
12345     10000001     AS1          2            2/16/2020
12345     10000002     D6T          2            4/20/2020
12345     10000003     COR          2            4/20/2020
12345     10000004     AS1          2             5/5/2020

Мой код для создания моей сводки пока выглядит следующим образом:

 SELECT
  DocNum,
  SUM(CASE WHEN ShippingDocSummary.StatusCode LIKE 'AS_' THEN StatusQty ELSE 0 END) AS AS_QTY,
  SUM(CASE WHEN ShippingDocSummary.StatusCode LIKE 'D6T' THEN StatusQty ELSE 0 END) AS D6T_QTY,
  SUM(CASE WHEN ShippingDocSummary.StatusCode LIKE 'COR' THEN StatusQty ELSE 0 END) AS COR_QTY,
  MAX(CASE WHEN ShippingDocSummary.StatusCode LIKE 'AS_' THEN DocHistID ELSE null END) AS LastAS_DHID
FROM ShippingDocSummary
GROUP BY DocNum

Это дает мне следующее, что правильно:

 DocNum    AS_QTY    D6T_QTY    COR_QTY    LastAS_DHID
12345     4         2          2          10000004

То, что я пытаюсь получить следующим в качестве столбца, StatusDate соответствует LastAS_DHID значению, которое было вычислено из выбора (т. Е. Мне Нужно, чтобы в следующем столбце отображалось ‘5/5/2020’, поскольку эта дата связана с DocHistID ‘10000004’).

В Power Bi я бы использовал lookupvalue функцию, но, поскольку я переношу свою логику на SQL server, я думаю, что мне нужно использовать производный CASE WHEN тип запроса, но не уверен, как структурировать синтаксис. Любая помощь приветствуется!

Комментарии:

1. Всегда ли даты в порядке возрастания параллельно идентификатору документа?

2. Все идентификаторы DocHistID уникальны для всей таблицы, поэтому они никогда не повторяются. Для данного номера документа наименьший идентификатор файла всегда будет первой записью для этого документа и обычно сопоставляется параллельно с датами состояния, но в реальной таблице могут быть десятки транзакций с разными кодами состояния и идентификаторами файлов, которые происходят в одну и ту же дату, и у даты нет метки времени, простодата. Кроме того, я не могу изменить информацию, поступающую в мою систему, чтобы добавить к ней временную метку, поэтому идентификатор — это уникальное поле, с которым я могу играть.

3. Я предполагал, что вы бы не спросили об этом, если бы это было так. Поскольку в примере не было ясно, я решил, что запрос, тем не менее, будет хорошей идеей.

4. Никогда не помешает уточнить, абсолютно.

Ответ №1:

Возможно, вам нужно создать свою таблицу on, а затем выполнить соединение по левому краю. При этом попробуйте следующее:

 SELECT A.DocNum,
A.AS_QTY,
A.D6T_QTY,
A.COR_QTY,
A.LastAS_DHID,
B.StatusDate

        FROM (
                SELECT DocNum,
                       SUM(CASE WHEN ShippingDocSummary.StatusCode LIKE 'AS_' THEN StatusQty ELSE 0 END) AS AS_QTY,
                       SUM(CASE WHEN ShippingDocSummary.StatusCode LIKE 'D6T' THEN StatusQty ELSE 0 END) AS D6T_QTY,
                       SUM(CASE WHEN ShippingDocSummary.StatusCode LIKE 'COR' THEN StatusQty ELSE 0 END) AS COR_QTY,
                       MAX(CASE WHEN ShippingDocSummary.StatusCode LIKE 'AS_' THEN DocHistID ELSE null END) AS LastAS_DHID
                
                       FROM ShippingDocSummary
                       GROUP BY DocNum) A
        LEFT JOIN TABLE_WITH_DOCHIST_10000004 B
        ON A.LastAS_DHID = B.DocHistID

Комментарии:

1. Я попробую и посмотрю, каковы результаты, спасибо

2. Это позаботилось об этом и дало мне ожидаемый результат, спасибо! Если у меня есть дополнительные столбцы, которые будут извлекаться таким же образом, понадобятся ли мне несколько вложенных операторов с несколькими объединениями?

Ответ №2:

 SELECT DISTINCT
  DocNum,
  SUM(CASE WHEN StatusCode LIKE 'AS_' THEN StatusQty ELSE 0 END) OVER (PARTITION BY DocNum) AS AS_QTY,
  SUM(CASE WHEN StatusCode LIKE 'D6T' THEN StatusQty ELSE 0 END) OVER (PARTITION BY DocNum) AS D6T_QTY,
  SUM(CASE WHEN StatusCode LIKE 'COR' THEN StatusQty ELSE 0 END) OVER (PARTITION BY DocNum) AS COR_QTY,
  FIRST_VALUE(StatusDate) OVER (PARTITION BY DocNum ORDER BY CASE WHEN StatusCode LIKE 'AS_' THEN DocHistID ELSE null END DESC) AS LastAS_DHID
FROM ShippingDocSummary

Я преобразовал ваши агрегированные функции в оконные версии с разделением на исходный столбец группировки. Затем select distinct необходимо фактически свернуть строки в одну агрегированную строку. Если first_value() оно недоступно на вашей платформе, есть другие альтернативы.

Вот тот, который работал для меня как скалярный подзапрос:

 (
SELECT StatusDate FROM ShippingDocSummary sds2
WHERE sds2.DocHistId =
  MAX(
    CASE WHEN ShippingDocSummary.StatusCode LIKE 'AS_'
         THEN ShippingDocSummary.DocHistID ELSE null END
  )
) AS LastAS_DHID