左联接返回重复行

Question

我有以下查询

SELECT
    [crm_quote_id],
    ISNULL(contact.Id, 2482) AS contactId,
    [crm_quote_company],
    [crm_quote_started_by_name],
    [crm_date_started],
    [crm_quote_status],
    [crm_quote_validity],
    [crm_quote_payment_terms],
    [address_line_1],
    [crm_quote_message_1],
    [crm_quote_message_2],
    [crm_quote_message]
FROM 
    [crmlive].[dbo].[crm_quotes] quote
LEFT JOIN 
    [CRMLiveWarwick].[dbo].[Contacts] contact 
      ON quote.address_line_1    = concat(contact.Firstname, ' ', contact.Surname)                                           
     AND quote.crm_quote_company = contact.CompanyId

[CRMLiveWarwick].[dbo].[Contacts]联系人在同一公司内有一个重复的全名(名字+‘'+姓)。这意味着它返回更多行，因为它们匹配。

我已经试过了

SELECT
    [crm_quote_id],
    MAX(ISNULL(contact.Id, 2482)) AS contactId,
    [crm_quote_company],
    [crm_quote_started_by_name],
    [crm_date_started],
    [crm_quote_status],
    [crm_quote_validity],
    [crm_quote_payment_terms],
    [address_line_1],
    [crm_quote_message_1],
    [crm_quote_message_2],
    [crm_quote_message]
FROM 
    [crmlive].[dbo].[crm_quotes] quote
LEFT JOIN 
    [CRMLiveWarwick].[dbo].[Contacts] contact 
      ON quote.address_line_1 = concat(contact.Firstname, ' ', contact.Surname)                                            
     AND quote.crm_quote_company = contact.CompanyId
GROUP BY 
    quote.crm_quote_id

因此，我得到了重复联系人的最大contact.Id，但是我得到了以下错误

列'crmlive.dbo.crm_quotes.crm_quote_company‘在select列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。

为了提供一些上下文，我将数据从旧数据库迁移到新数据库。旧数据库(crmlive.dbo.crm_quotes)没有联系人id，但是我可以通过查询包含名字和姓氏字段的联系人表来发现这一点。

Answer 1

另一种选择是使用ROW_NUMBER并选择第一级。就像这样：

;WITH cte AS (
    SELECT
        [crm_quote_id],
        ISNULL(contact.Id, 2482) AS ContactId,
        [crm_quote_company],
        [crm_quote_started_by_name],
        [crm_date_started],
        [crm_quote_status],
        [crm_quote_validity],
        [crm_quote_payment_terms],
        [address_line_1],
        [crm_quote_message_1],
        [crm_quote_message_2],
        [crm_quote_message],
        ROW_NUMBER() OVER (PARTITION BY quote ORDER BY ISNULL(contact.Id, 2482)) AS RowNo
    FROM 
        [crmlive].[dbo].[crm_quotes] quote
    LEFT JOIN 
        [CRMLiveWarwick].[dbo].[Contacts] contact ON quote.address_line_1 = concat(contact.Firstname, ' ', contact.Surname)
                                                  AND quote.crm_quote_company = contact.CompanyId
)
SELECT cte.*        -- or better, just the columns you need
FROM cte
WHERE RowNo = 1

性能可能比按所有这些列分组略差，但它的优点是可以轻松地在CTE中添加更多列，而不需要对它们进行分组。

此外，就性能而言，您应该考虑避免使用JOINing VARCHARS，特别是在像quote.address_line_1 = concat(contact.Firstname, ' ', contact.Surname)这样的表达式中，这些表达式对于大型集来说可能相当昂贵。

Answer 2

您需要将所有这些按组添加到子句中。

[crm_quote_company],
[crm_quote_started_by_name],
[crm_date_started],
[crm_quote_status],
[crm_quote_validity],
[crm_quote_payment_terms],
[address_line_1],
[crm_quote_message_1],
[crm_quote_message_2],
[crm_quote_message]

更新查询

SELECT
    [crm_quote_id],
    MAX(ISNULL(contact.Id, 2482)) AS contactId,
    [crm_quote_company],
    [crm_quote_started_by_name],
    [crm_date_started],
    [crm_quote_status],
    [crm_quote_validity],
    [crm_quote_payment_terms],
    [address_line_1],
    [crm_quote_message_1],
    [crm_quote_message_2],
    [crm_quote_message]
FROM [crmlive].[dbo].[crm_quotes] quote
LEFT JOIN [CRMLiveWarwick].[dbo].[Contacts] contact
    ON quote.address_line_1 = concat(contact.Firstname, ' ', contact.Surname)
    AND quote.crm_quote_company = contact.CompanyId
GROUP BY    quote.crm_quote_id,
            [crm_quote_company],
            [crm_quote_started_by_name],
            [crm_date_started],
            [crm_quote_status],
            [crm_quote_validity],
            [crm_quote_payment_terms],
            [address_line_1],
            [crm_quote_message_1],
            [crm_quote_message_2],
            [crm_quote_message]

Answer 3

我不会直接在join上使用Contact，那么，加入您想要的联系人的group，比如：

SELECT
    [crm_quote_id],
    max_contactId AS contactId,
    ...
FROM 
    [crmlive].[dbo].[crm_quotes] q LEFT outer JOIN 
    (select
        concat(Firstname, ' ', Surname) as contactname,
        CompanyId,
        MAX(ISNULL(Id, 2482)) AS max_contactId
     from
        [CRMLiveWarwick].[dbo].[Contacts]
     group by
        concat(Firstname, ' ', Surname), CompanyId) c ON 
     q.address_line_1 = contactname  AND 
     q.crm_quote_company = c.CompanyId