仅返回连接表中具有最新日期的行

debugcn 发表于 Dev

灾难小子

通过运行以下查询，我意识到我在列上有重复项QueryExecutionId。

SELECT DISTINCT qe.QueryExecutionid AS QueryExecutionId,
    wfi.workflowdefinitionid AS FlowId,
    qe.publishing_date AS [Date],
    c.typename AS [Type],
    c.name As Name
INTO #Send
FROM
    [QueryExecutions] qe  
    JOIN [Campaign] c ON qe.target_campaign_id = c.campaignid
    LEFT JOIN [WorkflowInstanceCampaignActivities] wfica ON wfica.queryexecutionresultid = qe.executionresultid
    LEFT JOIN [WorkflowInstances] wfi ON wfica.workflowinstanceid = wfi.workflowinstanceid
WHERE qe.[customer_idhash] IS NOT NULL;

例如，当我使用这些 QueryExecutionIds 之一进行测试时，我可以得到两个结果

select * from ##Send
where QueryExecutionId = 169237

我们意识到原因是这两行有不同的FlowId（第一个查询中的第二个返回值）。在讨论了这个问题之后，我们决定使用FlowId具有最新日期的记录。该日期是一个名为的列lastexecutiontime，位于第三个连接表中[WorkflowInstances]，该表也是 FlowId 来自的表。

如何仅获取QueryExecutionId具有最新WorkflowInstances.lastexecution时间值的唯一值并删除重复项？

佐哈尔·佩莱德

您可以使用按以下方式first_value分区的派生表：workflowinstanceidlastexecutiontime desc

SELECT DISTINCT qe.QueryExecutionid AS QueryExecutionId,
    wfi.FlowId,
    qe.publishing_date AS [Date],
    c.typename AS [Type],
    c.name As Name
INTO #Send
FROM
    [QueryExecutions] qe  
    JOIN [Campaign] c ON qe.target_campaign_id = c.campaignid
    LEFT JOIN [WorkflowInstanceCampaignActivities] wfica ON wfica.queryexecutionresultid = qe.executionresultid
    LEFT JOIN 
    (
        SELECT DISTINCT workflowinstanceid, FIRST_VALUE(workflowdefinitionid) OVER(PARTITION BY workflowinstanceid ORDER BY lastexecutiontime DESC) As FlowId
        FROM [WorkflowInstances]
    ) wfi ON wfica.workflowinstanceid = wfi.workflowinstanceid
WHERE qe.[customer_idhash] IS NOT NULL;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。