语句一:
--取分段起始时间
SELECT TJRQ
INTO START_DATE
FROM (SELECT TT1.TJRQ, ROWNUM ROWNO
FROM (
SELECT T.TJRQ TJRQ
FROM WORK_INFO_MAIN M, WORK_INFO_CHILD T, DJ_NSRXX D
WHERE M.SERIAL_NO = T.SERIAL_NO
AND T.NSRSBH = D.NSRSBH
AND T.YXZT_DM = '3'
AND (T.LSSJ = 0 OR T.LSSJ IS NULL)
AND D.CGS_FLAG = 1
AND M.CZRY_DM = C_USER.USER_ID
ORDER BY TJRQ
) TT1)
WHERE ROWNO = START_NUM;语句二:
---取分段结束时间
SELECT TJRQ
INTO END_DATE
FROM (SELECT TT1.TJRQ, ROWNUM ROWNO
FROM (
SELECT T.TJRQ TJRQ
FROM WORK_INFO_MAIN M, WORK_INFO_CHILD T, DJ_NSRXX D
WHERE M.SERIAL_NO = T.SERIAL_NO
AND T.NSRSBH = D.NSRSBH
AND T.YXZT_DM = '3'
AND (T.LSSJ = 0 OR T.LSSJ IS NULL)
AND D.CGS_FLAG = 1
AND M.CZRY_DM = C_USER.USER_ID
ORDER BY TJRQ
) TT1)
WHERE ROWNO = END_NUM;
--取分段起始时间
SELECT TJRQ
INTO START_DATE
FROM (SELECT TT1.TJRQ, ROWNUM ROWNO
FROM (
SELECT T.TJRQ TJRQ
FROM WORK_INFO_MAIN M, WORK_INFO_CHILD T, DJ_NSRXX D
WHERE M.SERIAL_NO = T.SERIAL_NO
AND T.NSRSBH = D.NSRSBH
AND T.YXZT_DM = '3'
AND (T.LSSJ = 0 OR T.LSSJ IS NULL)
AND D.CGS_FLAG = 1
AND M.CZRY_DM = C_USER.USER_ID
ORDER BY TJRQ
) TT1)
WHERE ROWNO = START_NUM;语句二:
---取分段结束时间
SELECT TJRQ
INTO END_DATE
FROM (SELECT TT1.TJRQ, ROWNUM ROWNO
FROM (
SELECT T.TJRQ TJRQ
FROM WORK_INFO_MAIN M, WORK_INFO_CHILD T, DJ_NSRXX D
WHERE M.SERIAL_NO = T.SERIAL_NO
AND T.NSRSBH = D.NSRSBH
AND T.YXZT_DM = '3'
AND (T.LSSJ = 0 OR T.LSSJ IS NULL)
AND D.CGS_FLAG = 1
AND M.CZRY_DM = C_USER.USER_ID
ORDER BY TJRQ
) TT1)
WHERE ROWNO = END_NUM;
解决方案 »
- spool小数的问题
- oracleservice实例服务启动不了,报错,发生系统错误 3,系统找不到指定的路径
- SOS,救命!请各位帮帮我,尽管发言!
- 怎么把表的第i行和第i+1行连接?
- 【请问】存储过程本来是VALID,为什么运行后从VALID变成INVALID
- oracle9i 安装中的问题
- 在PL/SQL中,在执行动态SQL中,什么时候要用到EXECUTE IMMEDIATE,谢谢
- 如何把oracle9i的数据库导入到oracle9中,大家给点意见。
- 用OLEDB如何连接ORACLE啊?
- 我将sqlserver中的表导入到oracle中,但产生空格怎么去掉?
- ORA-06530 引用未初始化的组合
- 一个序列与触发器的问题
INTO START_DATE
FROM (SELECT TT1.TJRQ, ROWNUM ROWNO
FROM (
SELECT T.TJRQ TJRQ
FROM WORK_INFO_MAIN M, WORK_INFO_CHILD T, DJ_NSRXX D
WHERE M.SERIAL_NO = T.SERIAL_NO
AND T.NSRSBH = D.NSRSBH
AND T.YXZT_DM = '3'
AND (T.LSSJ = 0 OR T.LSSJ IS NULL)
AND D.CGS_FLAG = 1
AND M.CZRY_DM = C_USER.USER_ID
ORDER BY TJRQ
) TT1)
WHERE ROWNO = START_NUM or ROWNO = END_NUM
INTO START_DATE
FROM (SELECT TT1.TJRQ, ROWNUM ROWNO
FROM (
SELECT T.TJRQ TJRQ
FROM WORK_INFO_MAIN M, WORK_INFO_CHILD T, DJ_NSRXX D
WHERE M.SERIAL_NO = T.SERIAL_NO
AND T.NSRSBH = D.NSRSBH
AND T.YXZT_DM = '3'
AND (T.LSSJ = 0 OR T.LSSJ IS NULL)
AND D.CGS_FLAG = 1
AND M.CZRY_DM = C_USER.USER_ID
ORDER BY TJRQ
) TT1)
WHERE ROWNO = START_NUM;
UNION
SELECT TJRQ
INTO END_DATE
FROM (SELECT TT1.TJRQ, ROWNUM ROWNO
FROM (
SELECT T.TJRQ TJRQ
FROM WORK_INFO_MAIN M, WORK_INFO_CHILD T, DJ_NSRXX D
WHERE M.SERIAL_NO = T.SERIAL_NO
AND T.NSRSBH = D.NSRSBH
AND T.YXZT_DM = '3'
AND (T.LSSJ = 0 OR T.LSSJ IS NULL)
AND D.CGS_FLAG = 1
AND M.CZRY_DM = C_USER.USER_ID
ORDER BY TJRQ
) TT1)
WHERE ROWNO = END_NUM;
INTO START_DATE
FROM (SELECT TT1.TJRQ, ROWNUM ROWNO
FROM (SELECT T.TJRQ TJRQ
FROM WORK_INFO_MAIN M, WORK_INFO_CHILD T, DJ_NSRXX D
WHERE M.SERIAL_NO = T.SERIAL_NO
AND T.NSRSBH = D.NSRSBH
AND T.YXZT_DM = '3'
AND (T.LSSJ = 0 OR T.LSSJ IS NULL)
AND D.CGS_FLAG = 1
AND M.CZRY_DM = C_USER.USER_ID
ORDER BY TJRQ
) TT1)
WHERE ROWNO = START_NUM
union all
SELECT TJRQ
INTO END_DATE
FROM (SELECT TT1.TJRQ, ROWNUM ROWNO
FROM (SELECT T.TJRQ TJRQ
FROM WORK_INFO_MAIN M, WORK_INFO_CHILD T, DJ_NSRXX D
WHERE M.SERIAL_NO = T.SERIAL_NO
AND T.NSRSBH = D.NSRSBH
AND T.YXZT_DM = '3'
AND (T.LSSJ = 0 OR T.LSSJ IS NULL)
AND D.CGS_FLAG = 1
AND M.CZRY_DM = C_USER.USER_ID
ORDER BY TJRQ
) TT1)
WHERE ROWNO = END_NUM
From (SELECT TT1.TJRQ STAR_TJRQ , TT1.TJRQ D_TJTQ ,ROWNUM ROWNO1,Rownum ROWNO2 FROM (SELECT T.TJRQ TJRQ FROM WORK_INFO_MAIN M, WORK_INFO_CHILD T, DJ_NSRXX D Where M.SERIAL_NO = T.SERIAL_NO AND T.NSRSBH = D.NSRSBH AND T.YXZT_DM = '3' AND (T.LSSJ = 0 OR T.LSSJ IS NULL) AND D.CGS_FLAG = 1 AND M.CZRY_DM = C_USER.USER_ID ORDER BY TJRQ ) TT1)
WHERE ROWNO2=START_NUM Or ROWNO1 = END_NUM ;
最大最小值而已,这么麻烦?