目前有这么一个需求
表结构大致如下
HQID,Recd
两列都是存储的文本数据
HQID中存储该文件需要会签的人员账号
例如:10001;10002;10003;10004
Recd中存储了历史操作记录
例如:10009于2012-05-12 08:30新添加记录/10002会签于2012-05-12 08:40/10003会签于2012-05-12 09:10
(HQID中有多少人需要会签的是由编制人员确定的,一个或多个)现有需求查询出暂未会签的人员ID
发通知提醒
即上例查询结果为:10001;10004
表结构大致如下
HQID,Recd
两列都是存储的文本数据
HQID中存储该文件需要会签的人员账号
例如:10001;10002;10003;10004
Recd中存储了历史操作记录
例如:10009于2012-05-12 08:30新添加记录/10002会签于2012-05-12 08:40/10003会签于2012-05-12 09:10
(HQID中有多少人需要会签的是由编制人员确定的,一个或多个)现有需求查询出暂未会签的人员ID
发通知提醒
即上例查询结果为:10001;10004
我把问题还简化了,实际还要驳回
驳回后又有部分人员又会签过的记录如下:
HQID Recd
10001;10002 10009于2012-05-12 08:30新添加记录/10002会签于2012-05-12 08:40
10003;10004 10005于2012-05-12 08:30新添加记录/10003会签于2012-05-12 08:35/10004会签于2012-05-12 08:50
[SYS@myoracle] SQL>WITH T AS
2 (SELECT '10001;10002;10003;10004' HQID,
3 '10009于2012-05-12 08:30新添加记录/10002会签于2012-05-12 08:40/10003会签于2012-05-12 09:10' RECD
4 FROM DUAL
5 UNION
6 SELECT '10001;10002;10003;10004;10005' HQID,
7 '10009于2012-05-13 08:30新添加记录/10002会签于2012-05-13 08:40/10004会签于2012-05-13 09:10' RECD
8 FROM DUAL),
9 T1 AS
10 (SELECT ROWNUM RN, HQID, RECD FROM T),
11 L AS
12 (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 20),
13 T2 AS
14 (SELECT RN, HQID, REGEXP_SUBSTR(HQID || ';', '[^;]+', 1, L.LV) HQ
15 FROM T1, L
16 WHERE L.LV <= LENGTH(HQID || ';') - LENGTH(REPLACE(HQID || ';', ';'))),
17 T3 AS
18 (SELECT RN,
19 RECD,
20 SUBSTR(REGEXP_SUBSTR(RECD, '[^/]+', 1, L.LV + 1), 1, 5) RE
21 FROM T1, L
22 WHERE L.LV <= LENGTH(RECD) - LENGTH(REPLACE(RECD, '/')))
23 SELECT T2.HQID,REPLACE(WM_CONCAT(T2.HQ),',',';') 没有会签
24 FROM T2
25 LEFT JOIN T3
26 ON T2.RN = T3.RN
27 AND T2.HQ = T3.RE
28 WHERE T3.RECD IS NULL
29 GROUP BY T2.HQID
30 ;HQID 没有会签
------------------------------ ------------------------------
10001;10002;10003;10004 10004;10001
10001;10002;10003;10004;10005 10003;10001;10005
SELECT
(
(case when recd_sub like '%'||first_hqid||'%' then '' else first_hqid end)
||(case when recd_sub like '%'||second_hqid||'%' then '' else ';'||second_hqid end)
||(case when recd_sub like '%'||third_hqid||'%' then '' else ';'||third_hqid end)
)
as NotAsignNo
FROM
(
---------------------------
select first_hqid,second_hqid
,(case when instr(other_hqid,';',1,1)>0 then
substr(other_hqid,1,instr(other_hqid,';',1,1)-1)
else other_hqid end) as third_hqid
,recd_sub
from
(
select first_hqid
,(case when instr(other_hqid,';',1,1)>0 then
substr(other_hqid,1,instr(other_hqid,';',1,1)-1)
else other_hqid end) as second_hqid
, RECD_SUB
,(case when instr(other_hqid,';',1,1)>0 then
substr(other_hqid,instr(other_hqid,';',1,1)+1,length(other_hqid)-instr(other_hqid,';',1,1))
else '' end) as other_hqid
from
(
SELECT
(case when INSTR(HQID,';',1,1)>0 then
SUBSTR(HQID,0,INSTR(HQID,';',1,1)-1) else hqid end) as first_hqid
,SUBSTR(HQID,INSTR(HQID,';',1,1)+1,LENGTH(HQID)-INSTR(HQID,';',1,1)) as other_hqid
,SUBSTR(RECD,INSTR(RECD,'/',1,1),LENGTH(RECD)-INSTR(RECD,'/',1,1)) AS RECD_SUB FROM A_TEST
) two
) three
--HQID这个字段中最多有多少个账号,就依次嵌套多少层查询
--这里只做了三个人员账号的例子
---------------------
) test