Добавление столбца для выбора оператора приносит все исторические данные

Question

Всем добрый вечер!

Я сталкиваюсь с действительно странной проблемой, которую мне трудно понять.

У меня есть 3 таблицы (таблица деталей, история перемещения деталей и таблица деталей).

То, что я пытаюсь сделать, это получить набор результатов, возвращающий номер лота, номер детали, описание продукта, количество, местоположение детали, то, что в настоящее время находится в запасе (по сравнению с полной историей) и кто последний перемещал продукт.

Теперь по запросу. Когда я запускаю приведенный ниже запрос, я получаю результирующий набор из 4751 строк; что идеально согласуется с моими ожидаемыми результатами. Однако, когда я пытаюсь добавить в поле ИД пользователя, я получаю набор результатов 186 573. Этот большой набор результатов, по-видимому, извлекает все исторические данные, а не просто сопоставляет идентификатор пользователя с 4751 нужными мне строками.

Из нужной мне таблицы деталей (prod_desc) Из нужной мне таблицы деталей (партия, номер детали, количество,prtlocation) Из нужной мне таблицы истории перемещения деталей (move_date, user_id)

4,751 Запрос:

SELECT DISTINCT
inv.lot,
inv.part#,
prt.prod_desc,
inv.lotquantity,
inv.prtlocation,
MAX(mv.move_date)AS 'Move Date'
FROM invdet AS inv
LEFT JOIN movetable AS mv ON inv.part# = mv.part#
LEFT JOIN partmstr AS prt ON inv.part# = prt.part#
WHERE inv.lot IS NOT NULL
GROUP BY inv.lot,inv.part#,prt.prod_desc,inv.lotquantity,inv.prtlocation
ORDER BY inv.prtlocation

186 573 Запрос:

SELECT DISTINCT
inv.lot,
inv.part#,
prt.prod_desc,
inv.lotquantity,
inv.prtlocation,
MAX(mv.move_date)AS 'Move Date'
mv.user_id
FROM invdet AS inv
LEFT JOIN movetable AS mv ON inv.part# = mv.part#
LEFT JOIN partmstr AS prt ON inv.part# = prt.part#
WHERE inv.lot IS NOT NULL
GROUP BY inv.lot,inv.part#,prt.prod_desc,inv.lotquantity,inv.prtlocation,mv.user_id
ORDER BY inv.prtlocation

Если я не использую функцию MAX, я не получаю текущий инвентарь и вместо этого получаю все результаты в таблице, которые мне не нужны. Я все еще учусь, и мои предложения GROUP BY оставляют желать лучшего, так как я все еще склоняюсь над этим (открыт для предложений!). Я уверен, что есть подзапрос, который я могу добавить сюда где-нибудь, но я все еще выясняю это. Любая помощь с благодарностью!

Answer 1

Я думаю, проблема в том, что когда вы вставляете mv.user_id из таблицы movetable, вы получаете все движения детали, а не только последнюю с датой max(mv.move_date). Один из способов - удалить левое соединение с подвижной таблицей и, возможно, использовать перекрестное

SELECT inv.lot,inv.part,prt.prod_desc,inv.lotquantity,inv.prtlocation,x.move_date,x.user_id
FROM invdet AS inv
CROSS APPLY(SELECT TOP 1
             mv.user_id,mv.move_date
            FROM movetable mv
            WHERE inv.part=mv.part
            ORDER BY mv.move_date DESC) AS x
LEFT JOIN partmstr AS prt ON inv.part=prt.part
WHERE inv.lot IS NOT NULL
ORDER BY inv.prtlocation

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

SELECT inv.lot,inv.part,prt.prod_desc,inv.lotquantity,inv.prtlocation,y.move_date,y.user_id
FROM invdet AS inv
LEFT JOIN(SELECT x.user_id,x.move_date,x.part
          FROM (SELECT mv.user_id,mv.move_date,mv.part,rn=ROWNUMBER() OVER(PARTITION BY mv.part ORDER BY mv.move_date DESC)
                FROM movetable mv) AS x
          WHERE x.rn=1) AS y ON y.part=inv.part
LEFT JOIN partmstr AS prt ON inv.part=prt.part
WHERE inv.lot IS NOT NULL
ORDER BY inv.prtlocation

Надеюсь, поможет.