例子
表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的：
- SQL code
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-2122 2 2011-06-2133 3 2011-06-2244 4 2011-06-23
------解决方案--------------------
CREATE OR REPLACE PROCEDURE P_TEST_SUM(
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 code
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-2122 2011-06-2133 2011-06-2244 2011-06-23 SQL>