只有当同一字段中的另一个值不存在时，我如何才能在MySQL中显示字段值？IF/ELSEIF或CASE？

Question

我正在写一份报告，显示过期的发票，我希望它包括公司的账单地址。数据可能有多种地址类型(收单方、企业地址、版税发票等)。这些都映射到一个id (address_name_type_id...1是“营业地址”，4是“收单方”。

每个组织都有一个业务地址。但我只想在其他地址不存在的情况下显示该地址。这段代码正确地显示了我主要想要的地址(其中ID是4、5或6)。但是对于那些只有“企业地址”(因为它不是4、5或6)的地址，它加载为空。

注意:这只是相关代码的一小段。整个报告很庞大，但我确实将其设置为每张发票只显示一行。在本例中，organization_id = 6303根本没有出现，因为它只有一个企业地址。

    SELECT
    o.name AS 'Organization',
    org_add.address_name_type_id AS "Address Type|display_name:AddressNameType",
    ad.street1 AS "Address 1",
    ad.street2 AS "Address 2",
    ad.city AS "City",
    ad.state_id AS  "State|display_name:State",
    ad.zip_code AS "Zip Code",
    ad.country_id AS "Country|display_name:Country"

    FROM
    organization o
    LEFT JOIN
        (SELECT o2a.organization_id, o2a.address_name, o2a.address_name_type_id, o2a.address_id, o2a.active 
           FROM organization2address o2a 
           WHERE o2a.address_name_type_id IN (4,5,6)) AS org_add ON org_add.organization_id = o.organization_id 
    LEFT JOIN
    address ad ON org_add.address_id = ad.address_id

WHERE o.organization_id IN (6654, 6082, 6303)
AND
org_add.active = 1

我试图修复的所有东西最终都只返回了企业地址，因为每个发票都有一个这样的地址。

如果没有address_name_type_id IN (4，5，6)，我怎么能告诉它只返回企业地址？此CASE语句不起作用-它只返回address_name_type_id为1的地址。

SELECT
   o.name AS 'Organization',
     (CASE WHEN o2a.address_name_type_id = 4 THEN 4 
           WHEN o2a.address_name_type_id = 5 THEN 5 
           WHEN o2a.address_name_type_id = 6 THEN 6 
            ELSE 1
             END)  AS "Address Type 2|display_name:AddressNameType",
   ad.street1 AS "Address 1",
   ad.street2 AS "Address 2",
   ad.city AS "City",
   ad.state_id AS  "State|display_name:State",
   ad.zip_code AS "Zip Code",
   ad.country_id AS "Country|display_name:Country"

    FROM
    organization o
    LEFT JOIN
    organization2address o2a ON o2a.organization_id = o.organization_id 
    LEFT JOIN
    address ad ON o2a.address_id = ad.address_id

WHERE o.organization_id IN (6654, 6082, 6303)
AND
o2a.active = 1
group by o.name

有可能做到这一点吗？我是不是想多了？

Answer 1

欢迎来到Stack Overflow。

如果我处在您的情况下，考虑到企业地址是最低的ID，我会对绑定到组织的address_name_type_id值执行MAX()。

如果出现任何大于1的值，则我们有另一个地址，并将从结果集中排除Business address。如果只出现1，则我们没有其他地址可提取，我将返回企业地址。

下面的代码显然是未经测试的，但其目的是让您了解下一步可以采取的方向：

SELECT
   o.name AS 'Organization',
   org_add.address_name_type_id AS "Address Type|display_name:AddressNameType",
   ad.street1 AS "Address 1",
   ad.street2 AS "Address 2",
   ad.city AS "City",
   ad.state_id AS  "State|display_name:State",
   ad.zip_code AS "Zip Code",
   ad.country_id AS "Country|display_name:Country"
FROM
   organization o
WHERE
   o.id IN ( SELECT organization_id FROM org_add WHERE MAX( address_name_type_id ) == 1 )
UNION
SELECT
   o.name AS 'Organization',
   org_add.address_name_type_id AS "Address Type|display_name:AddressNameType",
   alt_ad.street1 AS "Address 1",
   alt_ad.street2 AS "Address 2",
   alt_ad.city AS "City",
   alt_ad.state_id AS  "State|display_name:State",
   alt_ad.zip_code AS "Zip Code",
   alt_ad.country_id AS "Country|display_name:Country"
FROM
   organization o
WHERE
   o.id IN ( SELECT organization_id FROM org_add WHERE MAX( address_name_type_id ) > 1 )