有现成 sql server 的存储过程来实现,但是贴到oracle里面完全出错了,
oracle 里面 如何写 ?这是 sql的:
DECLARE @var_sql1 VARCHAR(max),@var_sql2 VARCHAR(max),@var_sql3 VARCHAR(max),@var_sql4 VARCHAR(max),@var_group VARCHAR(200) ,
@var_parm1 VARCHAR(max),@var_parm2 VARCHAR(max),
@var_parm3 VARCHAR(max),@var_group1 VARCHAR(200),@date_lyearbegin DATETIME,
@date_lyearend DATETIME,@date_lmonthbegin DATETIME,@date_lmonthend DATETIME--参数解析
--同期的时间SET @date_lyearbegin = DATEADD(year,-1,CAST (SUBSTRING(@var_parm,charindex('a.dsate_out,120) >= ',@var_parm)+20,10) AS DATETIME))
SET @date_lyearend = DATEADD(year,-1,CAST (SUBSTRING(@var_parm,charindex('a.dsate_out,120) <= ',@var_parm)+20,10) AS DATETIME))--环比的时间
SET @date_lmonthbegin = DATEADD(month,-1,CAST (SUBSTRING(@var_parm,charindex('a.dsate_out,120) >= ',@var_parm)+20,10) AS DATETIME))
SET @date_lmonthend = DATEADD(month,-1,CAST (SUBSTRING(@var_parm,charindex('a.dsate_out,120) <= ',@var_parm)+20,10) AS DATETIME))--同期的参数
SET @var_parm2 =SUBSTRING(@var_parm,0,charindex('a.dsate_out,120) >= ',@var_parm)+20) +convert(varchar(10),@date_lyearbegin,120)+''' '
+ SUBSTRING(@var_parm,charindex('a.dsate_out,120) >= ',@var_parm)+32,LEN(' and convert(varchar(10),a.dsate_out,120) <= ')+1) +convert(varchar(10),@date_lyearend,120) + ''' ' + SUBSTRING(@var_parm,charindex('a.dsate_out,120) <= ',@var_parm)+32,999)--环比的参数
SET @var_parm3 =SUBSTRING(@var_parm,0,charindex('a.dsate_out,120) >= ',@var_parm)+20) +convert(varchar(10),@date_lmonthbegin,120) +''' '
+ SUBSTRING(@var_parm,charindex('a.dsate_out,120) >= ',@var_parm)+32,LEN(' and convert(varchar(10),a.dsate_out,120) <= ')+1) +convert(varchar(10),@date_lmonthend,120) + ''' ' + SUBSTRING(@var_parm,charindex('a.dsate_out,120) <= ',@var_parm)+32,999)oracle
oracle 里面 如何写 ?这是 sql的:
DECLARE @var_sql1 VARCHAR(max),@var_sql2 VARCHAR(max),@var_sql3 VARCHAR(max),@var_sql4 VARCHAR(max),@var_group VARCHAR(200) ,
@var_parm1 VARCHAR(max),@var_parm2 VARCHAR(max),
@var_parm3 VARCHAR(max),@var_group1 VARCHAR(200),@date_lyearbegin DATETIME,
@date_lyearend DATETIME,@date_lmonthbegin DATETIME,@date_lmonthend DATETIME--参数解析
--同期的时间SET @date_lyearbegin = DATEADD(year,-1,CAST (SUBSTRING(@var_parm,charindex('a.dsate_out,120) >= ',@var_parm)+20,10) AS DATETIME))
SET @date_lyearend = DATEADD(year,-1,CAST (SUBSTRING(@var_parm,charindex('a.dsate_out,120) <= ',@var_parm)+20,10) AS DATETIME))--环比的时间
SET @date_lmonthbegin = DATEADD(month,-1,CAST (SUBSTRING(@var_parm,charindex('a.dsate_out,120) >= ',@var_parm)+20,10) AS DATETIME))
SET @date_lmonthend = DATEADD(month,-1,CAST (SUBSTRING(@var_parm,charindex('a.dsate_out,120) <= ',@var_parm)+20,10) AS DATETIME))--同期的参数
SET @var_parm2 =SUBSTRING(@var_parm,0,charindex('a.dsate_out,120) >= ',@var_parm)+20) +convert(varchar(10),@date_lyearbegin,120)+''' '
+ SUBSTRING(@var_parm,charindex('a.dsate_out,120) >= ',@var_parm)+32,LEN(' and convert(varchar(10),a.dsate_out,120) <= ')+1) +convert(varchar(10),@date_lyearend,120) + ''' ' + SUBSTRING(@var_parm,charindex('a.dsate_out,120) <= ',@var_parm)+32,999)--环比的参数
SET @var_parm3 =SUBSTRING(@var_parm,0,charindex('a.dsate_out,120) >= ',@var_parm)+20) +convert(varchar(10),@date_lmonthbegin,120) +''' '
+ SUBSTRING(@var_parm,charindex('a.dsate_out,120) >= ',@var_parm)+32,LEN(' and convert(varchar(10),a.dsate_out,120) <= ')+1) +convert(varchar(10),@date_lmonthend,120) + ''' ' + SUBSTRING(@var_parm,charindex('a.dsate_out,120) <= ',@var_parm)+32,999)oracle
解决方案 »
- oracle SQL 中 Number(10) 与 Number(5) 字段值的比较
- ASM 磁盘规划问题
- 一个关于ORACLE的STREAMS的问题
- 3 我的oracle是不是没有安装完全?请教大家了!谢谢!
- VB远程访问ORACLE
- 紧急求助:我改了oracle的sga和pga数据后,oracle数据库就不能启动了,提示错误为ora-27100:shared memory realm already exists
- 有没有像SQL SERVER里的跟踪器跟踪用户向数据库操作呢
- 怎样才能查看用户和表空间的关系?
- 大文本内容应怎样处理?
- 关于oracle绑定变量的问题
- 神奇的问题。SQL 错误: ORA-00911: 无效字符 00911. 00000 - "invalid character"
- 一个sql语句的写法问题
环比lag() over(partition by 分组字段 order by item)
with t1 as
(
select '001' item,2 qty,date'2012-01-01' dt from dual union all
select '002' item,2 qty,date'2012-02-01' dt from dual union all
select '003' item,3 qty,date'2012-03-01' dt from dual union all
select '001' item,5 qty,date'2013-01-01' dt from dual union all
select '002' item,4 qty,date'2013-02-01' dt from dual union all
select '003' item,6 qty,date'2013-03-01' dt from dual union all
select '001' item,1 qty,date'2014-01-01' dt from dual
)select item,qty,
lag(qty) over(order by dt) s_qty,
lag(qty) over(partition by item,to_char(dt,'mm') order by to_char(dt,'yyyy')) h_qty
from t1 item qty s_qty h_qty
----------------------------------------
1 001 2
2 002 2 2
3 003 3 2
4 001 5 3 2
5 002 4 5 2
6 003 6 4 3
7 001 1 6 5