ORGID VARCHAR2(32)
ORGNAME VARCHAR2(50) Y
PARENTORGID VARCHAR2(32) Y 没有父机构,为0
CREATEUSERID VARCHAR2(32) Y
ORGTYPE NUMBER Y 对应基础数据里的机构类别
ZHXGRQ DATE Y
如何写sql可以将 orgtype=1与orgtype=2的数据总数显示在同一招表里 sql
ORGNAME VARCHAR2(50) Y
PARENTORGID VARCHAR2(32) Y 没有父机构,为0
CREATEUSERID VARCHAR2(32) Y
ORGTYPE NUMBER Y 对应基础数据里的机构类别
ZHXGRQ DATE Y
如何写sql可以将 orgtype=1与orgtype=2的数据总数显示在同一招表里 sql
例如 select count(*) from orginfo group by orgtype
但是我的需求值得到一共总数是不行的 所以我希望将 现在每一条数据中的count 分开显示 也就是
orgid count(orgtype=1) count(orgtype=2)
1 3 4 这种形式
with
orginfo as (select '1' ORGID,'n1'ORGNAME,'0'PARENTORGID,'1'CREATEUSERID,2 ORGTYPE,sysdate ZHXGRQ from dual
union all
select '1' ORGID,'n1'ORGNAME,'0'PARENTORGID,'1'CREATEUSERID,2 ORGTYPE,sysdate ZHXGRQ from dual
union all
select '1' ORGID,'n1'ORGNAME,'0'PARENTORGID,'1'CREATEUSERID,3 ORGTYPE,sysdate ZHXGRQ from dual
union all
select '1' ORGID,'n1'ORGNAME,'0'PARENTORGID,'1'CREATEUSERID,3 ORGTYPE,sysdate ZHXGRQ from dual
union all
select '1' ORGID,'n1'ORGNAME,'0'PARENTORGID,'1'CREATEUSERID,3 ORGTYPE,sysdate ZHXGRQ from dual
union all
select '2' ORGID,'n2'ORGNAME,'0'PARENTORGID,'1'CREATEUSERID,2 ORGTYPE,sysdate ZHXGRQ from dual
union all
select '2' ORGID,'n2'ORGNAME,'0'PARENTORGID,'1'CREATEUSERID,3 ORGTYPE,sysdate ZHXGRQ from dual)
select orgid,sum(count_2)count_2,sum(count_3)count_3
from(select orgid,decode(orgtype,2,count(*),0)as count_2,decode(orgtype,3,count(*),0)as count_3
from orginfo
group by orgid,ORGTYPE)
group by orgid;
需要写这么复杂吗?下面这样就OK了、
select orgid,sum(decode(orgtype,2,1)),sum(decode(orgtype,3,1)) from orginfo group by orgid;
需要写这么复杂吗?下面这样就OK了、
select orgid,sum(decode(orgtype,2,1)),sum(decode(orgtype,3,1)) from orginfo group by orgid;
原来如此,用decode判断,符合就加1,原来这么简单。太厉害了