时间戳

首页 > 解决方案 > 仅当同一字段中的另一个值不存在时，如何在 MySQL 中显示字段值？IF/ELSEIF 还是 CASE？

mysql - 仅当同一字段中的另一个值不存在时，如何在 MySQL 中显示字段值？IF/ELSEIF 还是 CASE？

问题描述

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

每个组织都有一个公司地址。但我只希望在其他地址不存在时显示该地址。此代码正确地向我显示了我主要想要的地址（其中 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

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

标签： mysqlif-statementcase

解决方案

欢迎来到堆栈溢出。

如果我处于您的情况，考虑到公司地址是最低 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 )

推荐阅读