case when then else end如果判断条件太长，请问可以复用条件 或者在 then里面生成2个字段的结果吗？

[baidu_28726667]

 

SELECT '五合一水果罐头' AS name,'罐头加工' AS type,
CASE
    WHEN sum(safe_additive) > (condition...) 
    THEN '合格' ELSE '不合格' 
    END AS quality 
    FROM t_table		 
    WHERE additive_type= '一类';

上面能够判断到产品是 合格 还是 不合格，但是我还想要根据 合格 和 不合格 增加一个字段 储存后续处理，例如:

 

SELECT '五合一水果罐头' AS name,'罐头加工' AS type,
(CASE
    WHEN sum(safe_additive) > (condition...) 
    THEN '合格' ELSE '不合格' 
    END AS quality),
(CASE
    WHEN sum(safe_additive) > (condition...) 
    THEN '入库' ELSE '销毁' 
    END AS process),
    FROM t_table		 
    WHERE additive_type= '一类';

但是这里有个问题: condition.. 是一条非常长的 sql, 这样复制粘贴 两倍长度看着很夸张，有没有方法在上面的sql then的位置生成两个字段(quality 和 process)？ 或者下面case when 可以根据上面quality 来判断 生成 process 呢? 又或者其它方法？

[五楼442]

可以使用子查询来解决这个问题，这样就不需要重复写长长的条件了。你可以在SELECT子句中嵌套一个子查询，然后在外部的CASE WHEN语句中使用子查询的结果来生成quality和process字段。下面是一个示例：

sql

SELECT
name,
type,
quality,
(CASE
WHEN quality = '合格' THEN '入库'
ELSE '销毁'
END) AS process
FROM
(SELECT
'五合一水果罐头' AS name,
'罐头加工' AS type,
(CASE
WHEN SUM(safe_additive) > (SELECT condition... FROM dual) THEN '合格'
ELSE '不合格'
END) AS quality
FROM
t_table
WHERE
additive_type = '一类') AS subquery;

在这个查询中，内部的子查询计算了产品的quality，然后外部的SELECT语句使用了这个结果，并根据quality生成process字段。

[五楼442]

SELECT
name,
type,
quality,
(CASE
WHEN quality = '合格' THEN '入库'
ELSE '销毁'
END) AS process
FROM
(SELECT
'五合一水果罐头' AS name,
'罐头加工' AS type,
(CASE
WHEN SUM(safe_additive) > (SELECT condition... FROM dual) THEN '合格'
ELSE '不合格'
END) AS quality
FROM
t_table
WHERE
additive_type = '一类') AS subquery;

[五楼442]

SELECT
name,
type,
quality,
(CASE
WHEN quality = '合格' THEN '入库'
ELSE '销毁'
END) AS process
FROM
(SELECT
'五合一水果罐头' AS name,
'罐头加工' AS type,
(CASE
WHEN SUM(safe_additive) > (SELECT condition... FROM dual) THEN '合格'
ELSE '不合格'
END) AS quality
FROM
t_table
WHERE
additive_type = '一类') AS subquery;

[五楼442]

```sql
SELECT 
    name,
    type,
    quality,
    (CASE
        WHEN quality = '合格' THEN '入库'
        ELSE '销毁' 
    END) AS process
FROM
    (SELECT 
        '五合一水果罐头' AS name,
        '罐头加工' AS type,
        (CASE
            WHEN SUM(safe_additive) > (SELECT condition... FROM dual) THEN '合格' 
            ELSE '不合格' 
        END) AS quality
    FROM 
        t_table
    WHERE 
        additive_type = '一类') AS subquery;

```

[CSDN-Ada助手]

您的问题已经帮您同步至问答, 链接: https://ask.csdn.net/questions/8087950, 请您保持关注, 如果回答有帮助解决此问题, 麻烦您动动小手给相关的回答点赞, Ada会在评论区为您更新结题状态