Sql查询IN子句限制1记录

Question

我有一个查询，在其中我使用IN子句(对于2列、251和252)从表中获取记录。在理想情况下，251或252将有值，因此它应该工作良好，但在某些情况下，有些作业可能同时具有251或252值，但我只需要限制1基于jobHistoryID (最大值)记录。使用下面的查询，有时会得到2，如果在左联接中使用前1，它将获得null

SELECT 
    [Job].[JobID],
    [Job].[PriceEstimateNumber],
    [CustomerReadyDate].[JobHistoryDate] As [ConfirmLeadDate1],
    [CustomerReadyDate].[JobColumnID]
FROM
    [tblJob] Job
        ON [Job].[JobID] = [StepJob].[JobID]
        AND [Job].[SubmitTimestamp] > '2019-01-01'
    LEFT JOIN (
            SELECT 
                [JobHistory].[JobID],
                [JobHistory].[JobHistoryDate],
                [JobHistory].[JobColumnID],
                [JobHistory].[JobHistoryID],
            FROM [dbo].[tblJob_History] AS [JobHistory]
            INNER JOIN [dbo].[tblJob] AS [Job]
                ON [Job].[JobID] = [JobHistory].[JobID] 
            WHERE
                [JobHistory].[jobColumnID] IN (251,252)      
            ) AS [CustomerReadyDate]
            ON [CustomerReadyDate].[JobID] = [Job].[JobID]

WHERE
    [Job].[OrderType] = 'Job'
    AND [Job].LocationTypeID = 1

Answer 1

您可以根据ROW_NUMBER()添加一个jobHistoryID函数

SELECT 
    [Job].[JobID],
    [Job].[PriceEstimateNumber],
    [CustomerReadyDate].[JobHistoryDate] As [ConfirmLeadDate1],
    [CustomerReadyDate].[JobColumnID]
FROM
    [tblJob] Job
        ON [Job].[JobID] = [StepJob].[JobID]
        AND [Job].[SubmitTimestamp] > '2019-01-01'
    LEFT JOIN (
            SELECT 
                [JobHistory].[JobID],
                [JobHistory].[JobHistoryDate],
                [JobHistory].[JobColumnID],
                [JobHistory].[JobHistoryID],
                ROW_NUMBER() OVER(ORDER BY [JobHistoryID] DESC) rn
            FROM [dbo].[tblJob_History] AS [JobHistory]
            INNER JOIN [dbo].[tblJob] AS [Job]
                ON [Job].[JobID] = [JobHistory].[JobID] 
            WHERE
                [JobHistory].[jobColumnID] IN (251,252)      
            ) AS [CustomerReadyDate]
            ON [CustomerReadyDate].[JobID] = [Job].[JobID]

WHERE
    [Job].[OrderType] = 'Job'
    AND [Job].LocationTypeID = 1
    AND [CustomerReadyDate].rn = 1

但是，我认为您可以按以下方式优化查询：

SELECT * FROM (

    SELECT 
        [Job].[JobID],
        [Job].[PriceEstimateNumber],
        [JobHistory].[JobHistoryDate] As [ConfirmLeadDate1],
        [JobHistory].[JobColumnID]
        ROW_NUMBER() OVER(ORDER BY [JobHistoryID] DESC) rn
    FROM [dbo].[tblJob_History] AS [JobHistory]
    INNER JOIN [dbo].[tblJob] AS [Job]
        ON [Job].[JobID] = [JobHistory].[JobID] 
    WHERE [JobHistory].[jobColumnID] IN (251,252)   
    AND [Job].[OrderType] = 'Job'
    AND [Job].LocationTypeID = 1

) AS T1

WHERE T1.rn = 1 

Answer 2

也许您可以离开，加入历史表，接受按日期排序的第一条记录。

;WITH Data AS
(
    SELECT 
            [Job].[JobID],
            [Job].[PriceEstimateNumber],
            [CustomerReadyDate].[JobHistoryDate] As [ConfirmLeadDate1],
            [CustomerReadyDate].[JobColumnID],
            Instance = ROW_NUMBER() OVER(PARTITION BY Job.JobId ORDER BY [CustomerReadyDate.JobHistoryDate] DESC)
        FROM
            [tblJob] Job
                ON [Job].[JobID] = [StepJob].[JobID]
                AND [Job].[SubmitTimestamp] > '2019-01-01'
             LEFT JOIN [dbo].[JobHistory] AS [CustomerReadyDate]
                        ON [Job].[JobID] = [CustomerReadyDate].[JobID]  AND [CustomerReadyDate].[jobColumnID] IN (251,252)     
        WHERE
            [Job].[OrderType] = 'Job'
            AND [Job].LocationTypeID = 1

)
SELECT
   JobID,
   ConfirmLeadDate1,
   JobColumnID
FROM
    Data D
WHERE
    Instance = 1    

Answer 3

为了使查询正常工作，您似乎对查询进行了多次重写，并且引入了一些冗余(即在最内部的子查询中与tblJob进行冗余连接)。尝试以下查询以使其工作。

SELECT *
FROM (
    SELECT 
        [Job].[JobID],
        [Job].[PriceEstimateNumber]
        [JobHistory].[JobHistoryDate],
        [JobHistory].[JobColumnID],
        row_number() over (partition by [Job].[JobID] order by [JobHistory].[JobHistoryID] desc) AS [rn]
    FROM [dbo].[tblJob_History] AS [JobHistory]
    INNER JOIN [dbo].[tblJob] AS [Job]
        ON [Job].[JobID] = [JobHistory].[JobID] 
    WHERE
        [JobHistory].[JobColumnID] IN (251,252) AND
        [Job].[SubmitTimestamp] > '2019-01-01' AND
        [Job].[OrderType] = 'Job' AND 
        [Job].LocationTypeID = 1
) t
WHERE t.[rn] = 1