我已经存储了过程，过程的中间是一个查询地址表。
mailAddr表：
id | address1 | address2
oc表：
id | address1 | address2
如何生成一个查询来提取所有字段，取决于address1是否为NULL？
如果tableA.address1不为null，则需要所有这些字段。
如果tableA.address1为null，则需要tableB中的所有字段。
下面的查询给我一些问题：
select fc.*
,
(CASE
WHEN oc.Contributor_Address_Line_1__c IS NULL THEN mailAddr.Address_Line_1__c as MailingAddress1
WHEN oc.Contributor_Address_Line_1__c IS NULL THEN mailAddr.Institution__c as MailingInstitution
WHEN oc.Contributor_Address_Line_1__c IS NULL THEN mailAddr.Department__c as MailingDepartment
WHEN oc.Contributor_Address_Line_1__c IS NULL THEN mailAddr.Address_Line_2__c as MailingAddress2
WHEN oc.Contributor_Address_Line_1__c IS NULL THEN mailAddr.Address_Line_3__c as MailingAddress3
WHEN oc.Contributor_Address_Line_1__c IS NULL THEN mailAddr.City__c as MailingCity
WHEN oc.Contributor_Address_Line_1__c IS NULL THEN mailAddr.State_Province__c as MailingState
WHEN oc.Contributor_Address_Line_1__c IS NULL THEN mailAddr.ZIP_Postal_Code__c as MailingZip
c.Name as MailingCountryName
WHEN mailAddr.Address_Line_1__c IS NULL THEN
WHEN mailAddr.Address_Line_1__c IS NULL THEN oc.Contributor_Address_Line_1__c as MailingAddress1,
WHEN mailAddr.Address_Line_1__c IS NULL THEN oc.Contributor_Institution__c as MailingInstitution,
WHEN mailAddr.Address_Line_1__c IS NULL THEN oc.Contributor_Address_Line_1__c as MailingAddress1,
WHEN mailAddr.Address_Line_1__c IS NULL THEN oc.Contributor_Address_Line_2__c as MailingAddress2,
WHEN mailAddr.Address_Line_1__c IS NULL THEN oc.Contributor_City__c as MailingCity,
WHEN mailAddr.Address_Line_1__c IS NULL THEN oc.Contributor_State_Province__c as MailingState,
WHEN mailAddr.Address_Line_1__c IS NULL THEN oc.Contributor_ZIP_Postal_Code__c as MailingZip,
c.Name as MailingCountryName
END)
最佳答案
可能COALESCE对您来说足够了。
SELECT id
, COALESCE(A.address1, B.address1)
, COALESCE(A.address2, B.address2)
FROM TableA A
JOIN TableB B
ON A.id = B.id
关于php - MYSQL基于CASE查询多个字段，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59201385/