主页 / dba / 问题 / 249337
Accepted
Rahul Hendawe
Asked: 2019-09-22 00:39:09 +0800 CST

如何在子查询 JOIN 条件中使用父表列？

  • 772

我正在编写一个 SQL 查询，用于从多个表中提取记录，以便向用户发送更新或添加新值的电子邮件通知。

在下面的 SQL 查询中，我正在获取在各个表中更新的所有记录，但是我想在每个 LEFT JOIN 状态中为表的AssociatedRecordID列添加一个条件检查/JOIN，NotificationQueue这样我只会从每个选择表中提取匹配的记录来增加查询执行的性能。

例如：

LEFT JOIN Note nt WITH(NOLOCK) ON op.OpportunityID = nt.RecordID AND nt.NoteID = nq.AssociatedRecordID

这里我想nq.AssociatedRecordID用于加入AND nt.NoteID = nq.AssociatedRecordID

询问：

SELECT ops.* FROM NotificationQueue nq
    INNER JOIN (
        SELECT op.OpportunityID, op.Name AS [OpportunityName], ua.Email AS OwnerEmail, nt.NoteID, nt.Note,
            tks.TaskID, tks.ActivityType, ua.FirstName, ua.LastName, FORMAT(getdate(), 'M/dd/yyyy, hh:mm:ss tt') AS [datetime],
            lead.LeadID, lead.LeadName
        FROM Opportunity op WITH(NOLOCK) 
        INNER JOIN UserAccount ua WITH(NOLOCK) on op.owner = ua.UserID
        LEFT JOIN Note nt WITH(NOLOCK) ON op.OpportunityID = nt.RecordID AND nt.EntityID = 4 -- OPPORTUNITY
        LEFT JOIN (
            SELECT t.TaskID, t.TaskTypeID, t.OpportunityID, tt.Description AS [ActivityType] FROM Task t WITH(NOLOCK)
            INNER JOIN TaskType tt ON t.TaskTypeID = tt.TaskTypeID
        ) tks ON op.OpportunityID = tks.OpportunityID 
        LEFT JOIN (
            SELECT l.LeadID, l.Topic AS [LeadName], lo.AssociatedOpportunityId FROM LeadOpportunity lo WITH(NOLOCK)
            INNER JOIN Lead l ON lo.ParentLeadId = l.LeadID
            WHERE l.Active = 1
        ) lead ON op.OpportunityID = lead.AssociatedOpportunityId

        WHERE op.Active = 1 
    ) ops ON nq.RecordID = ops.OpportunityID        
WHERE nq.Processed = 'N'

输出： 在此处输入图像描述

我从上面的查询中得到了预期的记录，但我想在所有语句中使用 Table 的AssociatedRecordID列以获得更好的性能，任何建议我应该如何实现同样的效果将不胜感激。NotificationQueueLEFT JOIN

sql-server performance

1 个回答

  1. Best Answer

    您可能希望将EXISTS 运算符与子查询一起使用，但在不同的情况下服务，在您的情况下，将NotificationQueue表向下移动到子查询可以让您执行您正在寻找的连接，以下是相同的示例：

    SELECT  op.OpportunityID, 
        op.Name AS [OpportunityName], 
        ua.Email AS OwnerEmail, 
        nt.NoteID, nt.Note,
        tks.TaskID, 
        tks.ActivityType, 
        ua.FirstName, 
        ua.LastName, 
        FORMAT(getdate(), 'M/dd/yyyy, hh:mm:ss tt') AS [datetime],
        lead.LeadID, lead.LeadName
FROM NotificationQueue nq
    INNER JOIN  Opportunity op WITH (NOLOCK) ON nq.RecordID = op.OpportunityID  
    INNER JOIN  UserAccount ua WITH (NOLOCK) on op.owner = ua.UserID
    LEFT JOIN   Note nt WITH (NOLOCK) ON op.OpportunityID = nt.RecordID AND nt.EntityID = 4 -- OPPORTUNITY
    LEFT JOIN   (
                SELECT  t.TaskID, t.TaskTypeID, 
                        t.OpportunityID, 
                        tt.Description AS [ActivityType] 
                FROM    Task t WITH(NOLOCK)
                        INNER JOIN TaskType tt ON t.TaskTypeID = tt.TaskTypeID
                ) tks ON op.OpportunityID = tks.OpportunityID

    LEFT JOIN   (
                SELECT  l.LeadID, 
                        l.Topic AS [LeadName], 
                        lo.AssociatedOpportunityId 
                FROM    LeadOpportunity lo WITH(NOLOCK)
                        INNER JOIN Lead l ON lo.ParentLeadId = l.LeadID
                WHERE l.Active = 1
                ) lead ON op.OpportunityID = lead.AssociatedOpportunityId

WHERE op.Active = 1 and nq.Processed = 'N'

    根据评论，以下是 last 的示例LEFT JOIN：

        OUTER APPLY (
                SELECT  l.LeadID, 
                        l.Topic AS [LeadName], 
                        lo.AssociatedOpportunityId 
                FROM    LeadOpportunity lo WITH(NOLOCK)
                        INNER JOIN Lead l ON lo.ParentLeadId = l.LeadID
                WHERE l.Active = 1 AND nq.RecordID = lo.AssociatedOpportunityId
                ) lead --ON op.OpportunityID = lead.AssociatedOpportunityId
    • 2

相关问题