Abyss

create table INONEROW
(
  NO       NUMBER,
  COMMONID VARCHAR2(20),
  EA       VARCHAR2(20),
  PA       VARCHAR2(20),
  EB       VARCHAR2(20),
  PB       VARCHAR2(20),
  TYPE     VARCHAR2(2)
)

insert into INONEROW (NO, COMMONID, EA, PA, EB, PB, TYPE)
values (9, 'aaa', '33', null, '1', null, 'e');
insert into INONEROW (NO, COMMONID, EA, PA, EB, PB, TYPE)
values (1, 'aaa', '23', '24', '33', '43', 'e');
insert into INONEROW (NO, COMMONID, EA, PA, EB, PB, TYPE)
values (2, 'aaa', '32', '42', '33', '34', 's');
insert into INONEROW (NO, COMMONID, EA, PA, EB, PB, TYPE)
values (3, 'bb', '1', '2', '32', '31', 'e');
insert into INONEROW (NO, COMMONID, EA, PA, EB, PB, TYPE)
values (4, 'bb', '32', '31', '2', '1', 's');
insert into INONEROW (NO, COMMONID, EA, PA, EB, PB, TYPE)
values (5, 'c', '53', '22', '1', '1', 's');
insert into INONEROW (NO, COMMONID, EA, PA, EB, PB, TYPE)
values (6, 'd', '44', '23', '33', '44', 'e');
insert into INONEROW (NO, COMMONID, EA, PA, EB, PB, TYPE)
values (7, 'e', '33', '24', '22', '11', 'e');
insert into INONEROW (NO, COMMONID, EA, PA, EB, PB, TYPE)
values (8, 'f', '45', '25', null, '33', 's');
commit;

实现按照commonid分组，分别统计type=‘e’和type=‘s’的EA、PA、EB、PB

结果如图：

COMMONID                     EA        EA2         PA        PA2         EB        EB2         PB        PB2
-------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
e                             1          0          1          0          1          0          1          0
d                             1          0          1          0          1          0          1          0
aaa                           2          1          1          1          2          1          1          1
bb                            1          1          1          1          1          1          1          1
c                             0          1          0          1          0          1          0          1
f                             0          1          0          1          0          0          0          1

6 rows selected

LG设计人员思路：

LG：

select commonid,max(ea) ea,max(ea2) ea2,max(pa) pa,max(pa2) pa2,max(eb) eb,max(eb2) eb2,max(pb) pb,max(pb2) pb2
from
(
    select commonid,count(ea) ea,
    count(pa) pa,
    count(eb) eb,
    count(pb) pb,
    0 ea2,
    0 pa2,
    0 eb2,
    0 pb2
    from inonerow
    where type='e'
    group by commonid
    union all
    select commonid,
    0 ea,
    0 pa,
    0 eb,
    0 pb,
    count(ea) ea2,
    count(pa) pa2,
    count(eb) eb2,
    count(pb) pb2
    from inonerow
    where type='s'
    group by commonid
)
group by commonid

老薛：

:G设计人远并没有错，只是在此基础上研究优化.多学习一点，

思路:通过位运算判断，当即满足type条件和字段不为null条件count才加1

select commonid
,count(decode(bitand(decode(type,'e',1,0),decode(ea,null,0,1)),0,null,1)) ea
,count(decode(bitand(decode(type,'s',1,0),decode(ea,null,0,1)),0,null,1)) ea2
,count(decode(bitand(decode(type,'e',1,0),decode(pa,null,0,1)),0,null,1)) pa
,count(decode(bitand(decode(type,'s',1,0),decode(pa,null,0,1)),0,null,1)) pa2
,count(decode(bitand(decode(type,'e',1,0),decode(eb,null,0,1)),0,null,1)) eb
,count(decode(bitand(decode(type,'s',1,0),decode(eb,null,0,1)),0,null,1)) eb2
,count(decode(bitand(decode(type,'e',1,0),decode(pb,null,0,1)),0,null,1)) pb
,count(decode(bitand(decode(type,'s',1,0),decode(pb,null,0,1)),0,null,1)) pb2

from inonerow
group by commonid