时间戳

首页 > 解决方案 > 如何在Oracle中将多选查询转换为案例语句

sql - 如何在Oracle中将多选查询转换为案例语句

问题描述

我有 3 个选择查询，

select * 
from PERSON 
where person_status_id in (8) and updated_on > sysdate - 30;

select * 
from PERSON  
where person_status_id in (5, 7, 13) and updated_on > sysdate - 30;

select * 
from PERSON  
where person_status_id in (3, 4, 6) and updated_on > sysdate - 30

我只想要一个查询中的 3 个查询的计数，所以我将查询转换如下，

select distinct 
    v.cnt as "Completed",
    p.cnt as "Pending", 
    s.cnt as "Started" 
from 
    (select 1 as x, count(*) as cnt
     from PERSON w 
     where person_status_id in (3, 4, 6)
       and w.UPDATED_ON > (sysdate - 30)) v
inner join
    (select 1 as x, count(*) as cnt
     from PERSON w 
     where person_status_id in (5, 7, 13)
       and w.UPDATED_ON > (sysdate - 30)) p on v.x = p.x
inner join
    (select 1 as x, count(*) as cnt
     from PERSON w 
     where person_status_id in (8)
       and w.UPDATED_ON > (sysdate - 30)) s on p.x = s.x;

上述使用连接的查询按预期工作。它返回已完成、未决和已启动的计数。

现在我想转换为 case 语句。

select sum(case when person_status_id in (3,4,6) then (select 1 as x, count(*) as cnt from PERSON w and w.UPDATED_ON>  (sysdate-30) )v end) as Completed,
sum(case when person_status_id in (5,7,13) then (select 1 as x, count(*) as cnt from PERSON w and w.UPDATED_ON>  (sysdate-30) )p end) as Pending,
sum(case when  person_status_id in (8) then (select 1 as x, count(*) as cnt from PERSON w and w.UPDATED_ON>  (sysdate-30) )s end) as Started
from  PERSON w;

这显示“缺少右括号”错误。请纠正我哪里错了。提前致谢。

标签： sqloraclejoincaseoracle-sqldeveloper

解决方案

听起来您只需要对整个表进行一次条件聚合。限制person_status_id属于CASE表达式内部，而限制updated_on可以位于WHERE子句内部，因为它在所有聚合中共享。

SELECT
    COUNT(CASE WHEN person_status_id = 8 THEN 1 END) AS Completed,
    COUNT(CASE WHEN person_status_id IN (5, 7, 13) THEN 1 END) AS Pending,
    COUNT(CASE WHEN person_status_id IN (3, 4, 6) THEN 1 END) AS Started
FROM PERSON
WHERE updated_on > sysdate - 30;

推荐阅读