oracle job队列数,oracle 队列合计统计，请高手赐教

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

酒店值日表

id 主键

roomNum  房间号

people  值日人

time     值日时间

数据  /*有时一个房间需要联合值日，对于联合值日的，每个人统计项分别记录一次*/(房间号，值日人数量不定)

id      roomNum      people      time

1            2                A          2014-1-4

2            3                A,B       2014-1-10

3            4                B,C       2014-1-13

4            5                D          2014-1-18

5            1                E          2014-2-9

6            10              A,C,D    2014-2-14

7            3                A,E       2014-2-18

要实现的统计结果(以上表做全统计，统计不同值日人对不同房间值日次数)

房间号            A          B         C          D         合计

1

2               1                                               1

3               2          1                                   3

4                           1          1                       2

5                                                   1           1

6

7

8

9

10              1                      1           1          3

合计            4          2           2           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;

------解决思路----------------------

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;

------解决思路----------------------

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,'[^,]+')) 合计