SQL可选联接
SQL可选联接
提问于 2018-10-11 08:00:35
我有以下SQL语句：
SELECT
invoice.id "Invoice ID",
account.name "Owner"
FROM
invoice
LEFT OUTER JOIN account
on invoice.customerid = account.id但是，invoice.customerid可以是帐户或联系人。这是由发票表中的另一个字段customertype决定的。
那么，如何将连接更改为基于另一列的值连接到每行的不同表呢？
使用MS SQL Server。
回答 3
Stack Overflow用户
回答已采纳
发布于 2018-10-11 08:19:48
我假设您需要连接到另一个表' contact‘，并且您只想根据customertype列的值返回帐户名或联系人姓名。我假设customertype是一个字符串，可以是下面的'account‘或'contact’：
SELECT
invoice.id 'Invoice ID',
CASE invoice.customertype
WHEN 'account' THEN account.name
WHEN 'contact' THEN contact.name
END 'Owner'
FROM
invoice
LEFT OUTER JOIN account
ON invoice.customerid = account.id
LEFT OUTER JOIN contact
ON invoice.customerid = contact.idStack Overflow用户
发布于 2018-10-11 08:47:58
我会这样做：
SELECT i.id as Invoice_ID,
COALESCE(a.name, c.name) as Owner
FROM invoice i LEFT JOIN
account a
ON i.customerid = a.id AND i.customerType = 'Account' LEFT JOIN
contact c
ON i.customerid = c.id AND i.customerType = 'Contact';注意，这会将customerType逻辑合并到ON子句中。这应该会使JOIN更加准确。
Stack Overflow用户
发布于 2018-10-11 08:12:07
您可以在连接条件中使用CASE
SELECT invoice.id "Invoice ID",
IsNull(account.NAME,contact.NAME) "Owner"
FROM invoice
LEFT OUTER JOIN account
ON account.id = CASE
WHEN invoice.customertype = 'Account'
THEN invoice.customerid
ELSE NULL
END
LEFT OUTER JOIN contact
ON contact.id = CASE
WHEN invoice.customertype = 'contact'
THEN invoice.customerid
ELSE NULL
END 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52750319
复制相关文章
相似问题