取计数的oracle查询

Question

我有一张有组织名称和approval_status的桌子。Approval_status可以被批准为/rejected。如何使用oracle SQL查询和PLSQL过程为每个组织获取已批准和拒绝的记录的计数？

Answer 1

对于Oracle语句，一个选项是使用sum of decoded列，如下所示：

select organisation_name, 
       sum(decode(approval_status,'approved',1,0)) as "Count(Approved)",
       sum(decode(approval_status,'rejected',1,0)) as "Count(Rejected)"
  from tab
 group by organisation_name;

您可以创建一个存储过程，将这些计算值作为结果返回如下：

SQL> set serveroutput on;
SQL> create or replace procedure pr_get_org_count( 
                                             i_org_name tab.organisation_name%type,
                                             o_cnt_approved out pls_integer,
                                             o_cnt_rejected out pls_integer,
                                            ) is

begin
     select sum(decode(approval_status,'approved',1,0)) ,
            sum(decode(approval_status,'rejected',1,0))
       into o_cnt_approved, o_cnt_rejected
       from tab
      where organisation_name = i_org_name;

    dbms_output.put_line(' Approved Count is : '||o_cnt_approved);
    dbms_output.put_line(' Rejected Count is : '||o_cnt_rejected);
    -- these previous two line can be used to show the results through command line.
end;

如果您需要为每个组织输出多行，请在我们的过程中的游标中使用上述最分组的select语句，而不需要参数如下：

SQL> create or replace procedure pr_get_org_count is    
begin
    for c in
    (  
     select organisation_name,
            sum(decode(approval_status,'approved',1,0)) as cnt_approved ,
            sum(decode(approval_status,'rejected',1,0)) as cnt_rejected
       from tab
      group by organisation_name
    )
    loop
      dbms_output.put(' Organisation Name : '||c.organisation_name);        
      dbms_output.put(' [ Approved Count : '||c.cnt_approved);
      dbms_output.put_line(' - Rejected Count : '||c.cnt_rejected||' ]');
    end loop;
end;

编辑(与评论相关)您可以将此过程转换为下面的样式来发送结果：

create or replace procedure pr_get_org_count( 
                                             i_org_name tab.organisation_name%type,
                                             o_cnt_approved out pls_integer,
                                             o_cnt_rejected out pls_integer,
                                            ) is
  v_path  varchar2(4000):='http://www.mycompany.com.tr/main/default.aspx?email=';
  v_email varchar2(4000):='barbaros.ozhan@mycompany.com.tr';
  v_text  varchar2(4000):='My Results | for Approved : ';
  v_url   varchar2(4000);
  v_rep   varchar2(4000);
begin
  select sum(decode(approval_status,'approved',1,0)) ,
         sum(decode(approval_status,'rejected',1,0))
    into o_cnt_approved, o_cnt_rejected
    from tab
   where organisation_name = i_org_name;

   v_text := v_text||to_char(o_cnt_approved)||' - for Rejected : '||to_char(o_cnt_rejected);
   v_url  := v_path||'?email='||v_email||'&email_text='||v_text;
   v_rep  := utl_http.request(utl_url.escape(v_url, false, 'UTF-8'));
end;

Answer 2

下面有一个使用conditional aggregation和case语句的选项：

select organisation, 
       count(case when approval_status = 'approved' then 1 end) as approvedcount,
       count(case when approval_status = 'rejected' then 1 end) as rejectedcount
from yourtable
group by organisation