在联接子句中使用Case语句

Question

我想从多个表中获取数据，但不确定该如何操作。以下是我使用的查询语句，但它无法正常工作。请给予建议。

SELECT DISTINCT 
        VWL.Type, VWL.ID AS CompanyID,VWL.Name
    FROM dbo.LeadList_New AS VWL 
    LEFT OUTER JOIN
        CASE VWL.Type WHEN 'COMPANY' THEN
            CRMCompanyEmailAddress AS CE ON VWL.ID=CE.CRMCompanyID
        WHEN 'PERSON' THEN
            PersonEmailAddress AS PE ON VWL.ID=PE.PersonID
        END

- JSHunjan

---

任何特定的SQL语句都必须具有特定的“形状” - 涉及哪些表、列等，它们的类型等。更改查询中包含的表格的唯一方法是将其构建为字符串（动态SQL）。话虽如此，我不清楚您想要实现什么-您正在对连接左侧的列执行不同的操作，并且从右侧未选择任何列-即使根本没有发生连接，整个查询的结果也将相同。您能解释一下您想要实现什么吗？ - Damien_The_Unbeliever

请在描述中添加表格数据的示例。 - Alexander

Answer 1

使用CASE无法将表连接起来，你可能需要使用以下替代方法：

SELECT DISTINCT VWL.Type, VWL.ID AS CompanyID, VWL.Name, 
                CASE WHEN VWL.type = 'COMPANY' 
                    THEN CE.Name 
                    ELSE PE.Name 
                END AS EntityName 
FROM   dbo.leadlist_new AS VWL 
       LEFT OUTER JOIN crmcompanyemailaddress AS CE 
                    ON VWL.id = CE.crmcompanyid 
       LEFT OUTER JOIN personemailaddress AS PE 
                    ON VWL.id = PE.personid 

- Tim Schmelter

Answer 2

您应该将CASE语句移至SELECT语句中：

SELECT DISTINCT 
         VWL.Type, VWL.ID AS CompanyID,VWL.Name

        case VWL.Type 
              when 'COMPANY' then  CE.Address
              when 'PERSON' then PE.Address
        end


FROM        dbo.LeadList_New AS VWL  
LEFT OUTER JOIN CRMCompanyEmailAddress as CE on VWL.ID=CE.CRMCompanyID
LEFT OUTER JOIN PersonEmailAddress as PE on VWL.ID=PE.PersonID

或者只需将公司和个人两种声明联合起来。

SELECT VWL.Type, VWL.ID AS CompanyID,VWL.Name,CE.Address as Address 
FROM        dbo.LeadList_New AS VWL  
LEFT OUTER JOIN CRMCompanyEmailAddress as CE on VWL.ID=CE.CRMCompanyID
WHERE VWL.Type='COMPANY'

UNION

SELECT VWL.Type, VWL.ID AS CompanyID,VWL.Name,PE.Address as Address
FROM        dbo.LeadList_New AS VWL  
LEFT OUTER JOIN PersonEmailAddress as PE on VWL.ID=PE.PersonID
WHERE VWL.Type='PERSON'

- valex

---

在我看来，UNION是最好的选择。也许可以考虑使用UNION ALL。 - bjnr

@Dd2 题目发起者需要唯一行，因此我使用 UNION 来消除所有重复项。 - valex