具有一对多关系的SQL存储过程只返回最近的记录

Question

我一直在纠结这个问题。我创建了一个存储过程来返回相应的记录，但是其中一个表有多个打印作业，因此它返回多个记录。我只希望最近请求的打印作业返回，我不能让它正确工作。

请帮忙!

SELECT  PrintJobs.WORDERKey,
        PrintJobs.JobNumber, 
        WORDER.U_DateRequired AS DateRequired,
        ProductMaster.PartFileItemID, 
        WORDER.ManufacturingDepartment AS MfgDept,
        PrintJobs.Copies AS LabelCopies,
        PrintJobs.ExpiryDate AS JobExpiryDate,
        PrintJobs.Batch,
        PrintJobs.JulianDate AS JobJulianDate,
        ProductMaster.Description, 
        ProductMaster.PackFormat, 
        ProductMaster.IngDec, 
        ProductMaster.BarCodeNumber, 
        ProductMaster.CustomerProductCode, 
        ProductMaster.CriticalInstruction, 
        ProductMaster.StorageInstruction, 
        ProductMaster.MixedCaseTitle, 
        ProductMaster.MixedCode1, 
        ProductMaster.MixedCode2, 
        ProductMaster.MixedCode3, 
        ProductMaster.MixedCode4, 
        ProductMaster.MixedDescription1, 
        ProductMaster.MixedDescription2, 
        ProductMaster.MixedDescription3, 
        ProductMaster.MixedDescription4, 
        ProductMaster.MixedIngDec1, 
        ProductMaster.MixedIngDec2, 
        ProductMaster.MixedIngDec3, 
        ProductMaster.MixedIngDec4, 
        ProductMaster.CookingInstruction, 
        ProductMaster.LogoFilename,
        ProductMaster.ProductExpiryRule,
        ExpiryRulesMaster.GS1_AppID,
        ProductMaster.LabelTemplateID, 
        CASE WHEN ProductMaster.PartFileItemID IS NULL THEN 'N' ELSE 'Y' END AS LabelDefExists,
        LabelTemplateMaster.FileName, 
        CASE WHEN [DateFormat] = 'Default' THEN ExpiryRulesMaster.DateFormatString ELSE [DateFormat] END AS DateFormatString, 
        ExpiryRulesMaster.DateRounding, 
        ExpiryRulesMaster.ExcludeXmasNewYear,
        PART.ExternalShelfLifeTotalDays,
        CustomOptions.Tagline,
        CustomOptions.ShortName AS CustShortName,
        ExpiryRulesMaster.LabelText AS ExpiryRuleLabelText, 
        ProductMaster.inner_hDescription, 
        ProductMaster.inner_vDescription, 
        ProductMaster.inner_CustCode, 
        ProductMaster.inner_Storage, 
        ProductMaster.inner_WarningMsg, 
        ProductMaster.inner_Dateformat, 
        ProductMaster.inner_SpecialCode, 
        ProductMaster.LabelType,
        ProductMaster.inner_MicroSuiteCode, 
        ProductMaster.inner_PackWeight, 
        ProductMaster.inner_JDateFormat,
        ProductMaster.CustomOptionID, 
        ProductMaster.inner_PrintLogo,
        PART.NominalWeight,
        ProductMaster.IngDec_html, 
        ProductMaster.IngDec_Active, 
        PrintJobs.PrintJobsID,
        MaxJobs.MaxJob,
        PrintJobs.DateEntered
FROM        ProductMaster RIGHT OUTER JOIN
        (
            SELECT Partfileitemid,
                --JobNumber, 
                MAX(dateentered) MaxJob 
            FROM PrintJobs 
            GROUP BY PartFileItemID
        )
        MaxJobs ON ProductMaster.PartFileItemID = MaxJobs.PartFileItemID RIGHT OUTER       JOIN 
        PrintJobs ON PrintJobs.PartFileItemID = MaxJobs.PartFileItemID RIGHT OUTER JOIN
        LabelTemplateMaster ON ProductMaster.LabelTemplateID = LabelTemplateMaster.LabelTemplateID LEFT OUTER JOIN
        CustomOptions ON ProductMaster.CustomOptionID = CustomOptions.CustomOptionID LEFT OUTER JOIN
        ExpiryRulesMaster ON ProductMaster.ProductExpiryRule = ExpiryRulesMaster.ExpiryRule LEFT OUTER JOIN
        [F8Extract].[dbo].[WORDER] ON PrintJobs.WORDERKey = WORDER.WORDERKey LEFT OUTER JOIN
        [F8Extract].[dbo].[PART] ON ProductMaster.PartFileItemID = Part.PartFileItemID                  
WHERE       PrintJobs.WORDERKey = @WORDERKey

END

谢谢,

史黛西

Answer 1

一种方法是看TOP和ORDER BY，即

SELECT  TOP 1
        PrintJobs.WORDERKey,
        PrintJobs.JobNumber, 
        WORDER.U_DateRequired AS DateRequired,
        ProductMaster.PartFileItemID, 
        WORDER.ManufacturingDepartment AS MfgDept,
        PrintJobs.Copies AS LabelCopies,
        PrintJobs.ExpiryDate AS JobExpiryDate,
        PrintJobs.Batch,
        PrintJobs.JulianDate AS JobJulianDate,
        ProductMaster.Description, 
        ProductMaster.PackFormat, 
        ProductMaster.IngDec, 
        ProductMaster.BarCodeNumber, 
        ProductMaster.CustomerProductCode, 
        ProductMaster.CriticalInstruction, 
        ProductMaster.StorageInstruction, 
        ProductMaster.MixedCaseTitle, 
        ProductMaster.MixedCode1, 
        ProductMaster.MixedCode2, 
        ProductMaster.MixedCode3, 
        ProductMaster.MixedCode4, 
        ProductMaster.MixedDescription1, 
        ProductMaster.MixedDescription2, 
        ProductMaster.MixedDescription3, 
        ProductMaster.MixedDescription4, 
        ProductMaster.MixedIngDec1, 
        ProductMaster.MixedIngDec2, 
        ProductMaster.MixedIngDec3, 
        ProductMaster.MixedIngDec4, 
        ProductMaster.CookingInstruction, 
        ProductMaster.LogoFilename,
        ProductMaster.ProductExpiryRule,
        ExpiryRulesMaster.GS1_AppID,
        ProductMaster.LabelTemplateID, 
        CASE WHEN ProductMaster.PartFileItemID IS NULL THEN 'N' ELSE 'Y' END AS LabelDefExists,
        LabelTemplateMaster.FileName, 
        CASE WHEN [DateFormat] = 'Default' THEN ExpiryRulesMaster.DateFormatString ELSE [DateFormat] END AS DateFormatString, 
        ExpiryRulesMaster.DateRounding, 
        ExpiryRulesMaster.ExcludeXmasNewYear,
        PART.ExternalShelfLifeTotalDays,
        CustomOptions.Tagline,
        CustomOptions.ShortName AS CustShortName,
        ExpiryRulesMaster.LabelText AS ExpiryRuleLabelText, 
        ProductMaster.inner_hDescription, 
        ProductMaster.inner_vDescription, 
        ProductMaster.inner_CustCode, 
        ProductMaster.inner_Storage, 
        ProductMaster.inner_WarningMsg, 
        ProductMaster.inner_Dateformat, 
        ProductMaster.inner_SpecialCode, 
        ProductMaster.LabelType,
        ProductMaster.inner_MicroSuiteCode, 
        ProductMaster.inner_PackWeight, 
        ProductMaster.inner_JDateFormat,
        ProductMaster.CustomOptionID, 
        ProductMaster.inner_PrintLogo,
        PART.NominalWeight,
        ProductMaster.IngDec_html, 
        ProductMaster.IngDec_Active, 
        PrintJobs.PrintJobsID,
        MaxJobs.MaxJob,
        PrintJobs.DateEntered
FROM        ProductMaster RIGHT OUTER JOIN
        (
            SELECT Partfileitemid,
                --JobNumber, 
                MAX(dateentered) MaxJob 
            FROM PrintJobs 
            GROUP BY PartFileItemID
        )
        MaxJobs ON ProductMaster.PartFileItemID = MaxJobs.PartFileItemID RIGHT OUTER       JOIN 
        PrintJobs ON PrintJobs.PartFileItemID = MaxJobs.PartFileItemID RIGHT OUTER JOIN
        LabelTemplateMaster ON ProductMaster.LabelTemplateID = LabelTemplateMaster.LabelTemplateID LEFT OUTER JOIN
        CustomOptions ON ProductMaster.CustomOptionID = CustomOptions.CustomOptionID LEFT OUTER JOIN
        ExpiryRulesMaster ON ProductMaster.ProductExpiryRule = ExpiryRulesMaster.ExpiryRule LEFT OUTER JOIN
        [F8Extract].[dbo].[WORDER] ON PrintJobs.WORDERKey = WORDER.WORDERKey LEFT OUTER JOIN
        [F8Extract].[dbo].[PART] ON ProductMaster.PartFileItemID = Part.PartFileItemID                  
WHERE       PrintJobs.WORDERKey = @WORDERKey
ORDER BY PrintJobs.Number DESC

注意将PrintJobs.Number更改为指定时间顺序的字段