当字符串不属于一个组时，如何处理另一列为空的情况

Question

好的，所以我有两列：买方-订单

我如何写一份案例陈述来显示：

case when purchaser is blank, then orders is blank

我试过

case when purchaser = ' ' then orders = ' ' else orders end as OrdersEnd

Answer 1

正确的语法如下：

SELECT
    ...
,   CASE WHEN purchaser = '' THEN '' ELSE orders END AS OrdersEnd
FROM ...

请注意，"购买者为空"可能意味着"为空"。在这种情况下，语法应该是

CASE WHEN purchaser is NULL OR purchaser = '' THEN '' ELSE orders END AS OrdersEnd

Answer 2

试试这个：

case when purchaser = '' then '' else orders end as OrdersEnd

Answer 3

"blank"可能表示NULL或空格。您没有提到数据库，但以下内容应该适用于大多数情况：

(case when purchase is null or replace(purchaser, ' ', '') = '' then ' '
      else orders
 end) as OrdersEnd

Answer 4

您需要检查null和只有空格的购买者。

当购买者无效时，可能还有其他情况。。。你可以将这些添加为更多的WHEN部分：

SELECT 
  CASE WHEN purchaser is null THEN ''
       WHEN TRIM(purchaser) = '' THEN ''
       ELSE orders END as Orders
FROM table

现在我知道这是MS SQL，这是更短的

SELECT CASE WHEN TRIM(ISNULL(purchaser,'')) = '' THEN '' ELSE orders END AS Orders
FROM table

Answer 5

如果"blank"表示NULL，则可以使用特定于DB的函数。

Oracle：

SELECT NVL2(purchaser, orders, '') FROM MyTable;

MySQL：

SELECT IF(purchaser, orders, '') FROM MyTable;