oracle 行列合计统计，请高手赐教

Question

oracle

Answer 1

select roomNum,
sum(case when INSTR(','||people||',',',A,')>0 THEN 1 END) A,
sum(case when INSTR(','||people||',',',B,')>0 THEN 1 END) B,
sum(case when INSTR(','||people||',',',C,')>0 THEN 1 END) C,
sum(case when INSTR(','||people||',',',D,')>0 THEN 1 END) D,
sum(rexexp_count(people,'[^,]+')) 合计
from 酒店值日表
GROUP BY roomNum
ORDER BY roomNum

Answer 2

select roomnum,
sum(case when ','||people||',' like '%,A,%' then 1 end) A,
sum(case when ','||people||',' like '%,B,%' then 1 end) B,
sum(case when ','||people||',' like '%,C,%' then 1 end) C,
sum(case when ','||people||',' like '%,D,%' then 1 end) D,
sum(length(people)-length(replace(people,','))+1) "合计"
from 酒店值日表
group by roomnum
order by roomnum;

Answer 3

 WITH T AS
 (
 SELECT 1 ID,2 ROOMNUM,'A' PEOPLE,TO_DATE('2014-01-04','YYYY-MM-DD') TIME FROM DUAL UNION ALL
 SELECT 2 ID,3 ROOMNUM,'A,B' PEOPLE,TO_DATE('2014-01-10','YYYY-MM-DD') TIME FROM DUAL UNION ALL
 SELECT 3 ID,4 ROOMNUM,'B,C' PEOPLE,TO_DATE('2014-01-13','YYYY-MM-DD') TIME FROM DUAL UNION ALL
 SELECT 4 ID,5 ROOMNUM,'D' PEOPLE,TO_DATE('2014-01-18','YYYY-MM-DD') TIME FROM DUAL UNION ALL
 SELECT 5 ID,1 ROOMNUM,'E' PEOPLE,TO_DATE('2014-02-09','YYYY-MM-DD') TIME FROM DUAL UNION ALL
 SELECT 6 ID,10 ROOMNUM,'A,C,D' PEOPLE,TO_DATE('2014-02-14','YYYY-MM-DD') TIME FROM DUAL UNION ALL
 SELECT 7 ID,3 ROOMNUM,'A,E' PEOPLE,TO_DATE('2014-02-18','YYYY-MM-DD') TIME FROM DUAL 
 )
SELECT TT.*,
       A + B + C + D TOTAL FROM (SELECT T2.ROOM,
                                   (SELECT COUNT(*)
                                      FROM T
                                     WHERE T.ROOMNUM = T1.ROOMNUM
                                       AND T.PEOPLE LIKE '%A%') A,
                                   (SELECT COUNT(*)
                                      FROM T
                                     WHERE T.ROOMNUM = T1.ROOMNUM
                                       AND T.PEOPLE LIKE '%B%') B,
                                   (SELECT COUNT(*)
                                      FROM T
                                     WHERE T.ROOMNUM = T1.ROOMNUM
                                       AND T.PEOPLE LIKE '%C%') C,
                                   (SELECT COUNT(*)
                                      FROM T
                                     WHERE T.ROOMNUM = T1.ROOMNUM
                                       AND T.PEOPLE LIKE '%D%') D
                              FROM T T1,
                                   (SELECT MIN_ROOM + LEVEL - 1 ROOM
                                      FROM (SELECT MIN(ROOMNUM) MIN_ROOM,
                                                   MAX(ROOMNUM) MAX_ROOM
                                              FROM T)
                                    CONNECT BY MIN_ROOM + LEVEL - 1 <=
                                               MAX_ROOM) T2
                             WHERE T2.ROOM = T1.ROOMNUM(+)) TT ORDER BY ROOM;

Answer 4

上面的合计包含了A,B,C,D之外其他人的次数，修改下
select roomNum,
sum(case when INSTR(','||people||',',',A,')>0 THEN 1 END) A,
sum(case when INSTR(','||people||',',',B,')>0 THEN 1 END) B,
sum(case when INSTR(','||people||',',',C,')>0 THEN 1 END) C,
sum(case when INSTR(','||people||',',',D,')>0 THEN 1 END) D,
sum(case when INSTR(','||people||',',',A,')>0
           or INSTR(','||people||',',',B,')>0
           or INSTR(','||people||',',',C,')>0
           or INSTR(','||people||',',',D,')>0
           THEN 1 END)  合计
from 酒店值日表
GROUP BY roomNum)
ORDER BY roomNum

Answer 5

值日人不只限于A、B C D E,people这个字段保存的是人员字典值

Answer 6

再提供一种写法
with A AS(
select rownum RN from dual 
connect by rownum<=(select max(length(people)-length(replace(people,','))+1) from 酒店值日表)
)
SELECT ROOMNUM,
SUM(DECODE(NAME,'A',1)) A,
SUM(DECODE(NAME,'B',1)) B,
SUM(DECODE(NAME,'C',1)) C,
SUM(DECODE(NAME,'D',1)) D,
SUM(1) 合计
FROM(
SELECT B.ROOMNUM,REXEXP_SUBSTR(B.people,'[^,]+',1,A.RN) NAME
FROM A,酒店值日表 B
WHERE A.RN<=length(B.people)-length(replace(B.people,','))+1)
WHERE NAME IN ('A','B','C','D')
 

Answer 7

值日人不只限于A、B C D E,是个不定列，people这个字段保存的是人员字典值，请各位再给看一下

Answer 8

select roomnum,
sum(case when ','||people||',' like '%,A,%' then 1 end) A,
sum(case when ','||people||',' like '%,B,%' then 1 end) B,
sum(case when ','||people||',' like '%,C,%' then 1 end) C,
sum(case when ','||people||',' like '%,D,%' then 1 end) D,
sum(case when ','||people||',' like '%,E,%' then 1 end) E,
sum(length(people)-length(replace(people,','))+1) "合计"
from 酒店值日表
group by roomnum
order by roomnum;
合计项算法没变啊
11g后可以使用sum(rexexp_count(people,'[^,]+')) 合计