sql - 无论如何要缩短我的 BigQuery 脚本（for 循环？）

Question

好吧，我觉得我错过了一些非常基本的东西。TT

SELECT
    CATEGORY,
    PRODUCT,
    (CASE
         WHEN PRODUCT_DETAIL = 'STRAWBERRY' AND some_condition = x THEN 'REQUIRE REVIEW'
         WHEN PRODUCT_DETAIL = 'COD' AND some_condition = x THEN 'REQUIRE REVIEW'
         WHEN PRODUCT_DETAIL = 'MILK' AND some_condition = x THEN 'REQUIRE REVIEW'
         WHEN PRODUCT_DETAIL = 'BREAD' AND some_condition = x THEN 'REQUIRE REVIEW'
         ...
         WHEN PRODUCT_DETAIL = 'ICE CREAM' AND some_condition = x THEN 'REQUIRE REVIEW'
     ELSE 'GOOD'
     END) AS PRODUCT_CONDITION 

FROM
    MAIN_DATABASE

我想我的问题是因为 {AND some_condition = X THEN 'REQUIRE REVIEW'} 都是一样的，有没有办法将代码缩短为某种形式

    WHEN PRODUCT_DETAIL = ('STRAWBERRY', 'COD', 'MILK', ... , 'ICE CREAM') AND some_condition = x
    THEN 'REQUIRE REVIEW'
    ELSE 'GOOD'

或者它的一些变化？

谢谢，CN

标签： sqlgoogle-bigquery

Answer 1

您可以使用IN：

(CASE WHEN PRODUCT_DETAIL IN ('STRAWBERRY' , 'COD', . . . ) AND
           some_condition = x
      THEN 'REQUIRE REVIEW'
      ELSE 'GOOD'
 END) AS PRODUCT_CONDITION