具有多个子条件的嵌套案例

E.线圈

我在理解如何正确嵌套case语句时遇到了麻烦。（MSSQL Server 2012）

让我们给出下表。列StatusMissing是我要创建的

+------+--+------+--+------+--+------+--+------+--+------+--+---------------+
|  a1  |  |  a2  |  |  a3  |  |  b1  |  |  c1  |  |  d2  |  | StatusMissing |
+------+--+------+--+------+--+------+--+------+--+------+--+---------------+
| OK   |  | OK   |  | OK   |  | OK   |  | OK   |  | OK   |  | AllOK         |
| NULL |  | NULL |  | OK   |  | OK   |  | OK   |  | OK   |  | As            |
| OK   |  | NULL |  | OK   |  | OK   |  | OK   |  | OK   |  | As            |
| OK   |  | OK   |  | NULL |  | OK   |  | OK   |  | OK   |  | As            |
| OK   |  | OK   |  | OK   |  | NULL |  | OK   |  | OK   |  | B             |
| OK   |  | OK   |  | OK   |  | OK   |  | NULL |  | OK   |  | C             |
| OK   |  | OK   |  | OK   |  | OK   |  | OK   |  | NULL |  | D             |
| NULL |  | OK   |  | OK   |  | NULL |  | NULL |  | OK   |  | ABC           |
| NULL |  | OK   |  | OK   |  | OK   |  | NULL |  | NULL |  | ACD           |
| NULL |  | OK   |  | OK   |  | NULL |  | OK   |  | NULL |  | ABD           |
| NULL |  | OK   |  | OK   |  | NULL |  | NULL |  | NULL |  | ABCD          |
| NULL |  | OK   |  | OK   |  | OK   |  | NULL |  | NULL |  | ACD           |
| OK   |  | OK   |  | OK   |  | NULL |  | NULL |  | OK   |  | BC            |
| OK   |  | OK   |  | OK   |  | OK   |  | OK   |  | OK   |  | AllOK         |
| OK   |  | NULL |  | OK   |  | OK   |  | NULL |  | OK   |  | AC            |
| OK   |  | OK   |  | OK   |  | NULL |  | OK   |  | NULL |  | BD            |
| OK   |  | OK   |  | OK   |  | OK   |  | NULL |  | NULL |  | CD            |
+------+--+------+--+------+--+------+--+------+--+------+--+---------------+

首先，要了解嵌套的概念，我简化了表格：

+------+--+------+--+------+
|  a1  |  |  a2  |  |  b1  |
+------+--+------+--+------+
| OK   |  | OK   |  | OK   |
| OK   |  | OK   |  | NULL |
| OK   |  | NULL |  | OK   |
| NULL |  | OK   |  | OK   |
| NULL |  | NULL |  | OK   |
| NULL |  | OK   |  | NULL |
| OK   |  | NULL |  | NULL |
+------+--+------+--+------+

这些尝试导致这些失败。查询1

SELECT a1, a2, b1 'StatusMissing' =
CASE 
        WHEN a1 IS NULL
        THEN 
                CASE
                        WHEN a1 IS NULL
                        THEN 
                                CASE
                                        WHEN b1 IS NULL
                                        THEN 'AB'
                                END
                        ELSE 'A'
                END

        WHEN b1 IS NULL
        THEN 'B'
        ELSE 'AllOK'
END
FROM Table;

结果1：

+------+--+------+--+------+--+---------------+
|  a1  |  |  a2  |  |  b1  |  | StatusMissing |
+------+--+------+--+------+--+---------------+
| OK   |  | OK   |  | OK   |  | AllOK         |
| OK   |  | OK   |  | NULL |  | B             |
| OK   |  | NULL |  | OK   |  | AllOK         |
| NULL |  | OK   |  | OK   |  | NULL          |
| NULL |  | NULL |  | OK   |  | NULL          |
| NULL |  | OK   |  | NULL |  | AB            |
| OK   |  | NULL |  | NULL |  | B             |
+------+--+------+--+------+--+---------------+

Query2（其他为主要）

SELECT a1, a2, b1, 'Status' =
CASE 
        WHEN a1 IS NOT NULL AND a2 IS NOT NULL AND b1 IS NOT NULL
        THEN 'AllOK!'
        ELSE
                CASE
                        WHEN a2 IS NOT NULL OR a2 IS NOT NULL
                        THEN 
                                CASE
                                        WHEN b1 IS NULL
                                        THEN 'AB'
                                END

                        WHEN b1 IS NULL
                        THEN 'B'

                        ELSE 'A'
                END
END
FROM Table;

结果2

+------+--+------+--+------+--+---------------+
|  a1  |  |  a2  |  |  b1  |  | StatusMissing |
+------+--+------+--+------+--+---------------+
| OK   |  | OK   |  | OK   |  | AllOK         |
| OK   |  | OK   |  | NULL |  | AB            |
| OK   |  | NULL |  | OK   |  | A             |
| NULL |  | OK   |  | OK   |  | NULL          |
| NULL |  | NULL |  | OK   |  | A             |
| NULL |  | OK   |  | NULL |  | AB            |
| OK   |  | NULL |  | NULL |  | B             |
+------+--+------+--+------+--+---------------+

我到底在做什么错？我对SQL还是很陌生，因此，如果有适当的功能可以执行此操作，我将非常感谢您提供的信息！编辑：如果这样的话在SQL中是可能的，我的意思是：列StatusMissing ='missing'If（a1 == NULL）{StatusMissing + ='A'}

编辑2：

不存在StatusMissing列！我想使用下面的SQL语句创建它。SELECT ....状态=

所以基本上我只有A1，A2，B1（在简单表中）。请不要与第一个表混淆。它只是在这里显示它应该是什么样子。

戈登·利诺夫（Gordon Linoff）

我可能建议您对输出进行两个小修改：

代替“ As”，只需说“ A”。
而不是“ AllOK”，只需将该字段保留为空白。

通过这些修改，规则变得非常简单：

select t.*,
       ((case when a1 is null or a2 is null or a3 is null then 'A' else '' end) +
        (case when b1 is null then 'B' else '' end) +
        (case when c1 is null then 'C' else '' end) +
        (case when d1 is null then 'D' else '' end)
       ) as StatusMissing
from table t;

如果您确实需要版本，则子查询可能是最简单的方法：

select t. . . .,
       (case when StatusMissing = '' then 'AllOK'
             when StatusMissing = 'A' then 'As'
             else StatusMissing
        end) as StatusMissing
from (select t.*,
             ((case when a1 is null or a2 is null or a3 is null then 'A' else '' end) +
              (case when b1 is null then 'B' else '' end) +
              (case when c1 is null then 'C' else '' end) +
              (case when d1 is null then 'D' else '' end)
             ) as StatusMissing
      from table t
     ) t

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。