怎么写sql 才能将一个字段分成两个显示

2013-08-01

如何写sql 才能将一个字段分成两个显示ORGIDVARCHAR2(32)ORGNAMEVARCHAR2(50) YPARENTORGIDVARCHAR2(32) Y

如何写sql 才能将一个字段分成两个显示
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
[解决办法]
是这个意思吗？

            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;

[解决办法]

引用:

是这个意思吗？

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;

热点排行

oracle

怎么写sql 才能将一个字段分成两个显示