SQL Server 2008筛选 [英] SQL Server 2008 filtering

问题描述

我从同一个数据库连接4个表。我的查询返回的是我以后的数据，但也返回多行记录。我只想要为每个WKO_WorkOrderID使用最新的LastReportedHrsDate行。有人可以帮我用正确的代码来过滤吗？

代码（我还是很漂亮的SQL ..）：

  SELECT dbo.PLT.PLT_ItemID，SUM（dbo.PLD.PLD_IssueQty）AS Issued，dbo.WKO.WKO_WorkOrderID，dbo.WKO.WKO_RequiredQty，dbo.WKO.WKO_CompleteQty，
 dbo.WKO.WKO_RequiredQty  -  dbo.WKO.WKO_CompleteQty AS openbalance，dbo.PLT.PLT_QtyPerAssy，
（dbo.WKO.WKO_CompleteQty * dbo.PLT.PLT_QtyPerAssy  -  SUM（dbo.PLD.PLD_IssueQty））* -  1 AS WIP，dbo.WKO.WKO_ItemID，dbo.WOO.WOO_StatusCode，
 dbo.WOO.WOO_LastReportedHrsDate，dbo.WOO.WOO_WorkCenterID 
 FROM dbo.PLT INNER JOIN 
 dbo.PLD ON dbo.PLT.PLT_RecordID = dbo.PLD.PLD_PLT_RecordID INNER JOIN 
 dbo.WKO ON dbo.PLT.PLT_WorkOrderID = dbo.WKO.WKO_WorkOrderID LEFT OUTER JOIN 
 dbo.WOO ON dbo.PLT.PLT_WorkOrderID = dbo .WOO.WOO_WorkOrderID 
 WHERE（dbo.WKO.W KO_StatusCode = N'Released'）AND（dbo.PLT.PLT_ItemID ='9005-20-1794'）
 GROUP BY dbo.PLT.PLT_ItemID，dbo.WKO.WKO_WorkOrderID，dbo.WKO.WKO_RequiredQty，dbo.WKO .WKO_CompleteQty，dbo.PLT.PLT_QtyPerAssy，dbo.WKO.WKO_ItemID，
 dbo.WOO.WOO_StatusCode，dbo.WOO.WOO_LastReportedHrsDate，dbo.WOO.WOO_WorkCenterID 
 HAVING（SUM（dbo.PLD.PLD_IssueQty） = dbo.WKO.WKO_RequiredQty * dbo.PLT.PLT_QtyPerAssy）AND（dbo.WOO.WOO_LastReportedHrsDate IS NOT NULL）AND 
（dbo.WOO.WOO_StatusCode IS NOT NULL）
 ORDER BY dbo.WKO.WKO_WorkOrderID 
  

无法发布我的结果图片

解决方案

请试试这个：

  SELECT max（dbo.WOO.WOO_LastReportedHrsDate） ，dbo.PLT.PLT_ItemID，SUM（dbo.PLD.PLD_IssueQty）AS发布，dbo.WKO.WKO_WorkOrderID，dbo.WKO.WKO_RequiredQty，dbo.WKO.WKO_CompleteQty，
 dbo.WKO.WKO_RequiredQty  -  dbo.WKO .WKO_CompleteQty AS openbalance，dbo.PLT.PLT_QtyPerAssy，
（dbo.WKO.WKO_CompleteQty * dbo.PLT.PLT_QtyPerAssy  -  SUM（dbo.PLD.PLD_IssueQty））*  -  1 AS WIP，dbo.WKO.WKO_ItemID，dbo .WOO.WOO_StatusCode，
 dbo.WOO.WOO_WorkCenterID 
 FROM dbo.PLT INNER JOIN 
 dbo.PLD ON dbo.PLT.PLT_RecordID = dbo.PLD.PLD_PLT_RecordID INNER JOIN 
 dbo .WKO ON dbo.PLT.PLT_WorkOrderID = dbo.WKO.WKO_WorkOrderID LEFT OUTER JOIN 
 dbo.WOO ON dbo.PLT.PLT_WorkOrderID = dbo.WOO.WOO_WorkOrderID 
 WHERE（dbo.WKO.WKO_StatusCode = N'已发布'）AND（dbo.PLT.PLT_ItemID ='9005-20-1794'）
 AND（dbo.WOO.WOO_LastReportedHrsDate IS NOT NULL）AND（dbo.WOO.WOO_StatusCode is NOT NULL）
 GROUP BY dbo.PLT.PLT_ItemID，dbo.WKO.WKO_WorkOrderID，dbo.WKO.WKO_RequiredQty，dbo.WKO.WKO_CompleteQty，dbo.PLT.PLT_QtyPerAssy，dbo.WKO.WKO_ItemID，
 dbo.WOO.WOO_Status代码，dbo.WOO.WOO_WorkCenterID 
 HAVING（SUM（dbo.PLD.PLD_IssueQty）= dbo.WKO.WKO_RequiredQty * dbo.PLT.PLT_QtyPerAssy）
 ORDER BY dbo.WKO.WKO_WorkOrderID 
  

I am joining 4 tables together from the same db. My query is returning the data i am after, but is also returning multiple rows for some records. I only want the row with the most recent LastReportedHrsDate for each WKO_WorkOrderID. Can someone help me with the correct code to filter?

Code (I am still pretty new to SQL..):

SELECT     dbo.PLT.PLT_ItemID, SUM(dbo.PLD.PLD_IssueQty) AS Issued, dbo.WKO.WKO_WorkOrderID, dbo.WKO.WKO_RequiredQty, dbo.WKO.WKO_CompleteQty, 
                      dbo.WKO.WKO_RequiredQty - dbo.WKO.WKO_CompleteQty AS openbalance, dbo.PLT.PLT_QtyPerAssy, 
                      (dbo.WKO.WKO_CompleteQty * dbo.PLT.PLT_QtyPerAssy - SUM(dbo.PLD.PLD_IssueQty)) * - 1 AS WIP, dbo.WKO.WKO_ItemID, dbo.WOO.WOO_StatusCode, 
                      dbo.WOO.WOO_LastReportedHrsDate, dbo.WOO.WOO_WorkCenterID
FROM         dbo.PLT INNER JOIN
                      dbo.PLD ON dbo.PLT.PLT_RecordID = dbo.PLD.PLD_PLT_RecordID INNER JOIN
                      dbo.WKO ON dbo.PLT.PLT_WorkOrderID = dbo.WKO.WKO_WorkOrderID LEFT OUTER JOIN
                      dbo.WOO ON dbo.PLT.PLT_WorkOrderID = dbo.WOO.WOO_WorkOrderID
WHERE     (dbo.WKO.WKO_StatusCode = N'Released') AND (dbo.PLT.PLT_ItemID = '9005-20-1794')
GROUP BY dbo.PLT.PLT_ItemID, dbo.WKO.WKO_WorkOrderID, dbo.WKO.WKO_RequiredQty, dbo.WKO.WKO_CompleteQty, dbo.PLT.PLT_QtyPerAssy, dbo.WKO.WKO_ItemID, 
                      dbo.WOO.WOO_StatusCode, dbo.WOO.WOO_LastReportedHrsDate, dbo.WOO.WOO_WorkCenterID
HAVING      (SUM(dbo.PLD.PLD_IssueQty) = dbo.WKO.WKO_RequiredQty * dbo.PLT.PLT_QtyPerAssy) AND (dbo.WOO.WOO_LastReportedHrsDate IS NOT NULL) AND 
                      (dbo.WOO.WOO_StatusCode IS NOT NULL)
ORDER BY dbo.WKO.WKO_WorkOrderID

Can't post a pic of my results

解决方案

Please try this:

SELECT      max(dbo.WOO.WOO_LastReportedHrsDate),dbo.PLT.PLT_ItemID, SUM(dbo.PLD.PLD_IssueQty) AS Issued, dbo.WKO.WKO_WorkOrderID, dbo.WKO.WKO_RequiredQty, dbo.WKO.WKO_CompleteQty, 
                      dbo.WKO.WKO_RequiredQty - dbo.WKO.WKO_CompleteQty AS openbalance, dbo.PLT.PLT_QtyPerAssy, 
                      (dbo.WKO.WKO_CompleteQty * dbo.PLT.PLT_QtyPerAssy - SUM(dbo.PLD.PLD_IssueQty)) * - 1 AS WIP, dbo.WKO.WKO_ItemID, dbo.WOO.WOO_StatusCode, 
                      dbo.WOO.WOO_WorkCenterID
FROM         dbo.PLT INNER JOIN
                      dbo.PLD ON dbo.PLT.PLT_RecordID = dbo.PLD.PLD_PLT_RecordID INNER JOIN
                      dbo.WKO ON dbo.PLT.PLT_WorkOrderID = dbo.WKO.WKO_WorkOrderID LEFT OUTER JOIN
                      dbo.WOO ON dbo.PLT.PLT_WorkOrderID = dbo.WOO.WOO_WorkOrderID
WHERE     (dbo.WKO.WKO_StatusCode = N'Released') AND (dbo.PLT.PLT_ItemID = '9005-20-1794')
AND (dbo.WOO.WOO_LastReportedHrsDate IS NOT NULL) AND (dbo.WOO.WOO_StatusCode IS NOT NULL)
GROUP BY dbo.PLT.PLT_ItemID, dbo.WKO.WKO_WorkOrderID, dbo.WKO.WKO_RequiredQty, dbo.WKO.WKO_CompleteQty, dbo.PLT.PLT_QtyPerAssy, dbo.WKO.WKO_ItemID, 
                      dbo.WOO.WOO_StatusCode, dbo.WOO.WOO_WorkCenterID
HAVING      (SUM(dbo.PLD.PLD_IssueQty) = dbo.WKO.WKO_RequiredQty * dbo.PLT.PLT_QtyPerAssy) 
ORDER BY dbo.WKO.WKO_WorkOrderID

这篇关于SQL Server 2008筛选的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！