如何在postgresql查询中放置嵌套的case-when条件

Question

我想在查询中编写嵌套case when条件，以存储来自一个case when条件的值，以及将来自另一个case when条件的值存储到相同的新column.to中，从而获得这种结果。我将查询写为：

(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

在上面的来自CTE.so的查询demo.name列中，我的整个查询如下所示：

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 ;

如果第一个条件不匹配，我只想将(sq_name_new1)列的计数存储到独立可视列中，并将(demo.id)列的计数存储到同一列中。但是当我执行我的查询时，它抛出了一个error.that is，case when条件的语法中有一些错误。

请帮我写出正确的嵌套case-when条件。

Answer 1

CASE ... WHEN ... END是一个表达式。它可以像任何其他表达式一样嵌套。

CASE
     WHEN condition THEN
         CASE
             WHEN othercondition THEN
                 ....
         END
END

Answer 2

第一个分号;应该删除，就像@Craig Ringer的答案一样。

SELECT 
  CASE WHEN condition1 THEN
    CASE
      WHEN condition1.1 THEN
        ...
    END
  END AS column_name
FROM table_name;