如何在 postgresql 查询中放置嵌套的 case-when 条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40778444/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to put nested case-when condition in postgresql query
提问by shyarry g
i want to write nested case when condition in query to store the value that will come from one case when condition and another case when condition into same new column.to get this kind of result i am writing the query as:
我想在查询中的条件下编写嵌套案例，以存储来自一种条件时的值和另一种条件时的情况到同一新列中的值。为了获得这种结果，我将查询编写为：
(case when sq_name_new1 like format('%%%s%%',demo.name) THEN count(sq_name_new1) else (when demo.empcode is not null then count(demo.id) End) END) AS indivisual from res_scheduledjobs
in the above query demo.name column comes from CTE.so my whole query look like:
在上面的查询 demo.name 列中来自 CTE.so 我的整个查询看起来像：
with demo(empcode,id,name) as
(select hr_employee.emp_code,hr_employee.id,concat(resource_resource.name,' ',hr_employee.middle_name,' ',hr_employee.last_name) as name from hr_employee inner join resource_resource on resource_resource.id=hr_employee.resource_id)
select demo.empcode,demo.name,sq_name_new1,(case when sq_name_new1 like format('%%%s%%',demo.name) THEN count(sq_name_new1) else (when demo.empcode is not null then count(demo.id) End) END) AS indivisual from res_scheduledjobs LEFT JOIN demo on demo.id=res_scheduledjobs.assigned_technician group by res_scheduledjobs.assigned_technician,sq_name_new1,demo.empcode,demo.name ;
i just want to store the count of (sq_name_new1) column into INDIVISUAL Column and the count of (demo.id) column into same column,that is in INDIVISUAL,if the first case condition does not match. but when i am executing my query it throw an error.that is,something is wrong in the syntax of case when condition.
我只想将 (sq_name_new1) 列的计数存储到 INDIVISUAL 列中，并将 (demo.id) 列的计数存储到同一列中，即在 INDIVISUAL 中，如果第一种情况条件不匹配。但是当我执行我的查询时，它会抛出一个错误。也就是说，case when 条件的语法有问题。
please help me yo write the correct nested case-when condition.
请帮我写出正确的嵌套 case-when 条件。
回答by Craig Ringer
CASE ... WHEN ... ENDis an expression. It can be nested like any other expression.
CASE ... WHEN ... END是一个表达式。它可以像任何其他表达式一样嵌套。
CASE
WHEN condition THEN
CASE
WHEN othercondition THEN
....
END
END
回答by Guoyang Qin
The first semicolon ;should be removed as in @Craig Ringer's answer.
;应该删除第一个分号，如@Craig Ringer 的回答所示。
SELECT
CASE WHEN condition1 THEN
CASE
WHEN condition1.1 THEN
...
END
END AS column_name
FROM table_name;
