添加列以选择语句带来所有历史数据

Question

晚上好！

我遇到了一个非常奇怪的问题，我很难理解。

我有3个表（零件表，零件移动历史记录和零件细节表）。

我要做的是让结果集返回批次＃，零件＃，产品描述，数量，部分位置，当前库存中的内容（与完整的历史记录）以及谁上次移动产品。

现在，用于查询。当我运行以下查询时，我将获得一个4,751行的结果集；这与我的预期结果完美匹配。但是，当我尝试添加UserID字段时，我将获得186,573的结果集。这个大结果集似乎吸引了所有历史数据，而将用户ID与我实际需要的4,751行匹配。

我需要的零件表（prod_desc）从零件细节表中我需要（批次，零件＃，locquantity，protlocation）从零件移动历史表中我需要的表（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

如果我不使用最大函数，则不会获取当前库存，而是将所有结果都在表中获取，而我不需要。我仍在学习，我的团队待在我仍然围绕着它的头（向建议开放！）时，我的小组有很多值得需要的东西。我敢肯定，我可以在这里扔一个子查询，但我仍然弄清楚这些。任何帮助都非常感谢！

Answer 1

我认为问题是，当您从表可移动中插入mv.user_id时，您会获得所有零件的动作，而不仅仅是带有date 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 Table中的每行执行一个子查询。如果它太慢，您可以用户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

希望它有帮助。