Sql server阻止下一个联接(如果上一个找到任何发现)。

Question

从技术角度来看，我只想在以前的联接找到结果时阻止后续联接。

从业务的角度来看，我有一个简单的客户和前景列表，这些客户和前景都符合一项政策。我有列出分配给一个具有重复状态列的策略的所有前景的功能。

• client_duplication列是基于事实的，已经有了具有类似data
• prospect_duplication列的客户端。

最重要的部分是，每当出现客户端复制时，我不想检查可能的复制。

我的想法是做这样的事情：

select p.*, client_duplication.status, prospect_duplication.status
from prospect p
cross apply (
   //find any first client duplication 
   select top 1
   case 
        when inner_prospect_uuid.id is not null then 'CLIENT_SAME_UUID'
        when inner_prospect_lastname.id is not null then 'CLIENT_SAME_LASTNAME'
        else null 
   end as status
   from prospect outer_prospect
   left join client inner_client on inner_client.lastname = outer_prospect.lastname
   left join prospect inner_prospect_uuid on inner_prospect_uuid.uuid = outer_prospect.uuid 
   left join prospect inner_prospect_lastname on inner_prospect_lastname.lastname = outer_prospect.lastname 
   where outer_prospect.id = p.id
) client_duplication 
cross apply (
   //find any first prospect duplication ONLY when client duplications is null
   select top 1 
   case 
        when inner_prospect_uuid.id is not null then 'PROSPECT_SAME_UUID'
        when inner_prospect_lastname.id is not null then 'PROSPECT_SAME_LASTNAME'
        else null 
   end as status
   from prospect outer_prospect
   left join prospect inner_prospect_lastname on inner_prospect_lastname.lastname = outer_prospect.lastname and inner_prospect_lastname.id != outer_prospect.id
   left join prospect inner_prospect_uuid on inner_prospect_uuid.uuid = outer_prospect.uuid and inner_prospect_uuid.id != outer_prospect.id
   where outer_prospect.id = p.id
) prospect_duplication
where p.policy_id = 123 
OFFSET 0 ROWS
FETCH NEXT 100 ROWS ONLY;

在这种方法中，我看到了两个问题：

我一直叫applies

• Second

1. 都是交叉的，没有比交叉应用更好的机制了吗？我担心，每当两个表

中都有数兆的记录时，它就会杀死我的应用程序。

Answer 1

我不明白你为客户做的连接和潜在的复制。但是我们也可以使用CROSS APPLY代替LEFT JOIN

我真的不明白下面的连接，与下面的连接您再次加入到相同的记录。inner_prospect_uuid和outer_prospect引用相同的记录。

LEFT JOIN prospect inner_prospect_uuid ON inner_prospect_uuid.uuid = outer_prospect.uuid

更新的查询：

SELECT p.*
    ,COALESCE(client_duplication.STATUS, prospect_duplication.STATUS) [Status]
FROM prospect p
LEFT JOIN (
    --find any first client duplication 
    SELECT TOP 1 outer_prospect.id [Id]
        ,CASE 
            WHEN inner_prospect_uuid.id IS NOT NULL
                THEN 'CLIENT_SAME_UUID'
            WHEN inner_prospect_lastname.id IS NOT NULL
                THEN 'CLIENT_SAME_LASTNAME'
            ELSE NULL
            END AS STATUS
    FROM prospect outer_prospect
    --left join client inner_client on inner_client.lastname = outer_prospect.lastname
    LEFT JOIN prospect inner_prospect_uuid ON inner_prospect_uuid.uuid = outer_prospect.uuid
    LEFT JOIN prospect inner_prospect_lastname ON inner_prospect_lastname.lastname = outer_prospect.lastname
    WHERE outer_prospect.id = p.id
    ) client_duplication ON client_duplication.Id = p.Id
LEFT JOIN (
    --find any first prospect duplication ONLY when client duplications is null
    SELECT TOP 1 outer_prospect.id [Id]
        ,CASE 
            WHEN inner_prospect_uuid.id IS NOT NULL
                THEN 'PROSPECT_SAME_UUID'
            WHEN inner_prospect_lastname.id IS NOT NULL
                THEN 'PROSPECT_SAME_LASTNAME'
            ELSE NULL
            END AS STATUS
    FROM prospect outer_prospect
    LEFT JOIN prospect inner_prospect_lastname ON inner_prospect_lastname.lastname = outer_prospect.lastname
        AND inner_prospect_lastname.id != outer_prospect.id
    LEFT JOIN prospect inner_prospect_uuid ON inner_prospect_lastname.uuid = outer_prospect.uuid
        AND inner_prospect_uuid.id != outer_prospect.id
    WHERE outer_prospect.id = p.id
    ) prospect_duplication ON prospect_duplication.Id = p.Id
WHERE p.policy_id = 123 
OFFSET 0 ROWS
FETCH NEXT 100 ROWS ONLY;

Answer 2

这个问题的典型解决方案总是同时执行两个联接，但是在SELECT子句中使用一个SELECT操作符来选择第一个选项，因此只有当第一个选项不匹配时，第二个选项才会出现。