如何嵌套多个MAX(..)语句在一个情况下查询

Question

我正在使用PostgreSQL，目前我有这样的东西:

SELECT MAX(CASE WHEN SUBSTRING(article_code,5,1) IN ('9') THEN 1 ELSE 0 END) AS has9,
       MAX(CASE WHEN SUBSTRING(article_code,5,1) IN ('8') THEN 1 ELSE 0 END) AS has8
FROM xxxx

但是我想要的是这样的:

CASE WHEN 
    (MAX(CASE WHEN SUBSTRING(article_code,5,1) IN ('9') = 1 THEN 'has9' ELSE
    MAX(CASE WHEN SUBSTRING(article_code,5,1) IN ('8') = 1 THEN 'has8' ELSE 'FIX'
END as test_version

到目前为止没有成功。这可能吗?

Answer 1

在第一个示例中，您使用MAX函数将单个article_code列转换为两个不同的列(has9和has8)。在第二个示例中，您不再将article_code列拆分为多个列，因此，据我所知，您不再需要MAX函数。

您是否尝试过以下内容?

SELECT CASE WHEN SUBSTRING(article_code,5,1) IN ('9') THEN 'has9'
            ELSE SUBSTRING(article_code,5,1) IN ('8') THEN 'has8'
            ELSE 'FIX'
       END as test_version
FROM xxxx

EDIT:啊，在这种情况下，您仍然需要MAX函数将其减少到一行。

您应该能够使用您的原始查询作为获得单行的子查询，然后使用CASE WHEN将其转换为单个字符串:

SELECT CASE WHEN has9 = 1 THEN 'has9'
            WHEN has8 = 1 THEN 'has8'
            ELSE 'FIX'
       END as test_version
FROM (  SELECT MAX(CASE WHEN SUBSTRING(article_code,5,1) IN ('9') THEN 1 ELSE 0 END) AS has9,
               MAX(CASE WHEN SUBSTRING(article_code,5,1) IN ('8') THEN 1 ELSE 0 END) AS has8
        FROM xxxx )

或者，您可以使用我之前的查询作为子查询，并使用MAX函数将其减少到单行:

SELECT CASE WHEN MAX(result_rank) = 3 THEN 'has9'
            WHEN MAX(result_rank) = 2 THEN 'has8'
            ELSE 'FIX'
       END as test_version
FROM ( SELECT CASE WHEN SUBSTRING(article_code,5,1) IN ('9') THEN 3
                   ELSE SUBSTRING(article_code,5,1) IN ('8') THEN 2
                   ELSE 1
              END as result_rank
       FROM xxxx )

Answer 2

我认为应该是:

SELECT 
      CASE 
           WHEN EXISTS(SELECT article_code 
                         FROM XXXX 
                        WHERE SUBSTRING(article_code,5,1) = '9')
           THEN 'has9'
           WHEN EXISTS(SELECT article_code 
                         FROM XXXX 
                        WHERE SUBSTRING(article_code,5,1) = '8')
           THEN 'has8'
           ELSE 'FIX'
END CASE  as test_version;