Блог

У меня есть три таблицы, которые необходимо объединить, чтобы получить взамен ежемесячные данные инвентаризации.

Таблица 1: TargetInventory
Таблица 2: targetValue
Таблица 3: TargetWeight

[TargetInventory] не изменяется после добавления в первый раз.

[targetValue] — это всего лишь небольшая таблица, которая включает цены на различные виды металла.

[TargetWeight] обновляется ежемесячно в рамках нашего процесса инвентаризации. Мы ВСТАВЛЯЕМ новые данные, мы никогда не ОБНОВЛЯЕМ старые данные.

Ниже приведена взаимосвязь между этими таблицами. (Извините, у меня нет очков репутации для публикации изображения. Здесь совершенно новый, так что, надеюсь, это имеет смысл.)

 (* = UniqueKey)

--TargetValue--      --TargetInventory--  --TargetWeight--
*MaterialID <===|    *TargetID <=====|    *ID
 Material       |===> MaterialID     |===> TargetID
 PricePerOunce        Length               RecordDate
 Density              Width                Weight
                      Thickness
                      DateInInventory
  

Таблица TargetWeight содержит несколько записей для TargetID (поскольку новая добавляется каждый месяц при инвентаризации). Это хорошо для меня, чтобы отслеживать историческое использование, но для текущей стоимости запасов мне нужен только самый последний целевой вес.Возвращаемый вес.

Я не знаю, как выполнить ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ из другого ВНУТРЕННЕГО СОЕДИНЕНИЯ, поэтому я в недоумении, как это сделать (не переключаясь на MySQL и просто выполняя ОГРАНИЧЕНИЕ 1 …)

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

 SELECT
TargetInventory.TargetID AS TargetInventory_TargetID, 
TargetInventory.MaterialID AS TargetInventory_MaterialID, 
TargetInventory.Length, 
TargetInventory.Width, 
TargetInventory.Thickness, 
TargetValue.MaterialID AS TargetValue_MaterialID, 
TargetValue.PricePerOunce, 
TargetValue.Density, 
TargetWeight.ID, 
TargetWeight.TargetID AS TargetWeight_TargetID, 
TargetWeight.RecordDate, 
TargetWeight.Weight
FROM
(TargetValue 
    INNER JOIN TargetInventory 
    ON TargetValue.[MaterialID] = TargetInventory.[MaterialID]
)
CROSS APPLY (
     SELECT TOP 1 *
     FROM .....
)
  

Следующий запрос работает для меня в Access 2010. Он использует ВНУТРЕННЕЕ СОЕДИНЕНИЕ в подзапросе, чтобы заменить ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ (которое Access SQL не поддерживает). Предполагается, что для данного (TargetID, recordDate) будет не более одной записи [TargetWeight]:

 SELECT 
    TargetInventory.TargetID AS TargetInventory_TargetID, 
    TargetInventory.MaterialID AS TargetInventory_MaterialID, 
    TargetInventory.Length, 
    TargetInventory.Width, 
    TargetInventory.Thickness, 
    TargetValue.MaterialID AS TargetValue_MaterialID, 
    TargetValue.PricePerOunce, 
    TargetValue.Density, 
    LatestWeight.ID, 
    LatestWeight.TargetID AS TargetWeight_TargetID, 
    LatestWeight.RecordDate, 
    LatestWeight.Weight
FROM 
    (
        TargetValue 
        INNER JOIN 
        TargetInventory 
            ON TargetValue.[MaterialID] = TargetInventory.[MaterialID]
    ) 
    INNER JOIN 
    (
        SELECT tw.*
        FROM
            TargetWeight AS tw
            INNER JOIN
            (
                SELECT TargetID, MAX(RecordDate) AS LatestDate
                FROM TargetWeight
                GROUP BY TargetID
            ) AS latest
                ON latest.TargetID=tw.TargetID
                    AND latest.LatestDate=tw.RecordDate
    ) AS LatestWeight
        ON LatestWeight.TargetID = TargetInventory.TargetID
  

Альтернативный подход специально для Access 2010 или более поздней версии

Если приведенный выше запрос зависает с большим количеством строк в [TargetWeight], то другим возможным решением для Access 2010 было бы добавить Yes/No поле с именем [Current] в таблицу [TargetWeight] и использовать следующий после вставки данных макрос, чтобы гарантировать, что только последняя запись для каждого [TargetID]помечается как [Текущий]:

Как только это будет сделано, запрос будет просто

 SELECT 
    TargetInventory.TargetID AS TargetInventory_TargetID, 
    TargetInventory.MaterialID AS TargetInventory_MaterialID, 
    TargetInventory.Length, 
    TargetInventory.Width, 
    TargetInventory.Thickness, 
    TargetValue.MaterialID AS TargetValue_MaterialID, 
    TargetValue.PricePerOunce, 
    TargetValue.Density, 
    TargetWeight.ID, 
    TargetWeight.TargetID AS TargetWeight_TargetID, 
    TargetWeight.RecordDate, 
    TargetWeight.Weight
FROM 
    (
        TargetValue 
        INNER JOIN 
        TargetInventory 
            ON TargetValue.[MaterialID] = TargetInventory.[MaterialID]
    ) 
    INNER JOIN 
    TargetWeight 
        ON TargetInventory.TargetID = TargetWeight.TargetID
WHERE TargetWeight.Current = True;
  

Чтобы максимизировать производительность, [TargetWeight] .[TargetID] и [TargetWeight] .[Текущие] поля должны быть проиндексированы.

 SELECT      TargetInventory.TargetID AS TargetInventory_TargetID, 
            TargetInventory.MaterialID AS TargetInventory_MaterialID, 
            TargetInventory.Length, 
            TargetInventory.Width, 
            TargetInventory.Thickness, 
            TargetValue.MaterialID AS TargetValue_MaterialID, 
            TargetValue.PricePerOunce, 
            TargetValue.Density,        Weight.ID, 
            Weight.TargetID AS TargetWeight_TargetID,
            Weight.RecordDate, 
            Weight.Weight
FROM        TargetInventory
INNER JOIN  TargetValue ON TargetValue.[MaterialID] = TargetInventory.[MaterialID]
CROSS APPLY (
                SELECT TOP 1 *
                FROM    TargetWeight
                WHERE   TargetID = TargetInventory.TargetID
                ORDER BY RecordDate DESC
            )   AS Weight