例子
表1
row_id item_no
11 1
22 2
33 3
44 4表2
item_no date
1,2 2011-06-21
3 2011-06-22
4 2011-06-23
用什么方式写出来 效果是
11 1 2011-06-21
22 2 2011-06-21
33 3 2011-06-22
44 4 2011-06-23
表1
row_id item_no
11 1
22 2
33 3
44 4表2
item_no date
1,2 2011-06-21
3 2011-06-22
4 2011-06-23
用什么方式写出来 效果是
11 1 2011-06-21
22 2 2011-06-21
33 3 2011-06-22
44 4 2011-06-23
SQL> WITH t1 AS (
2 SELECT '11' row_id,'1' item_no FROM DUAL UNION ALL
3 SELECT '22' ,'2' FROM DUAL UNION ALL
4 SELECT '33' ,'3' FROM DUAL UNION ALL
5 SELECT '44' ,'4' FROM DUAL
6 ),t2 AS (
7 SELECT '1,2' item_no,'2011-06-21' idate FROM DUAL UNION ALL
8 SELECT '3' ,'2011-06-22' FROM DUAL UNION ALL
9 SELECT '4' ,'2011-06-23' FROM DUAL
10 )
11 SELECT t1.*,
12 t2.idate
13 FROM t1,
14 t2
15 WHERE instr(',' || t2.item_no || ',', ',' || t1.item_no || ',') > 0;ROW_ IT IDATE
---- -- --------------------
11 1 2011-06-21
22 2 2011-06-21
33 3 2011-06-22
44 4 2011-06-23
begin
select t1.row_id,t1.item_no,t2.date from table1 t1 left join table2 t2 where t1.item_no = t2.item_no;
end;
select t1.row_id,t1.item_no,t2.idate
from t1,t2
where instr(t2.item_no,t1.item_no)>0
/
SQL>
SQL> WITH t1 AS (
2 SELECT '11' row_id,'1' item_no FROM DUAL UNION ALL
3 SELECT '22' ,'2' FROM DUAL UNION ALL
4 SELECT '33' ,'3' FROM DUAL UNION ALL
5 SELECT '44' ,'4' FROM DUAL)
6 , t2 AS (
7 SELECT '1,2' item_no,'2011-06-21' idate FROM DUAL UNION ALL
8 SELECT '3' ,'2011-06-22' FROM DUAL UNION ALL
9 SELECT '4' ,'2011-06-23' FROM DUAL)
10 select t1.row_id,t1.item_no,t2.idate
11 from t1,t2
12 where instr(t2.item_no,t1.item_no)>0
13 /
ROW_ID ITEM_NO IDATE
------ ------- ----------
11 1 2011-06-21
22 2 2011-06-21
33 3 2011-06-22
44 4 2011-06-23
条件也满足了
e.g.:
SELECT INSTR('11,12,13',',3,') FROM dual d
i_BeginTime IN VARCHAR2,
i_EndTime IN VARCHAR2
)AS
v_BeginTime VARCHAR2(20); -- 结算记录起始时间
v_EndTime VARCHAR2(20); -- 结算记录结束时间
BEGIN
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END P_TEST_SUM;
楼主结构就这样了,中间的逻辑可以随便加
SQL>
SQL> WITH t1 AS (
2 SELECT '11' row_id,'1' item_no FROM DUAL UNION ALL
3 SELECT '22' ,'2' FROM DUAL UNION ALL
4 SELECT '33' ,'3' FROM DUAL UNION ALL
5 SELECT '44' ,'4' FROM DUAL UNION ALL
6 SELECT '55' ,'5' FROM DUAL)
7 , t2 AS (
8 SELECT '1,2,50' item_no,'2011-06-21' idate FROM DUAL UNION ALL
9 SELECT '3' ,'2011-06-22' FROM DUAL UNION ALL
10 SELECT '4' ,'2011-06-23' FROM DUAL)
11 select t1.row_id,t3.idate
12 from t1,(
13 select distinct regexp_substr(item_no,'[^,]+',1,level) item_no,idate
14 from t2
15 connect by level<=length(item_no)-length(replace(item_no,',',''))+1) t3
16 where t1.item_no=t3.item_no
17 /
ROW_ID IDATE
------ ----------
11 2011-06-21
22 2011-06-21
33 2011-06-22
44 2011-06-23
SQL>