因为在演示系统函数用法时,经常需要创建一张表,再插入数据,挺麻烦。在DB2中可以这样做:
select
name,
salary,
lag(salary,0) over(order by salary) as lag0,
lag(salary) over(order by salary) as lag1,
lag(salary,2) over(order by salary) as lag2,
lag(salary,3,0,'IGNORE NULLS') over(order by salary) as lag3,
lag(salary,4,-1,'RESPECT NULLS') over(order by salary) as lag4,
lead(salary) over(order by salary) as lead
from (
values
('张三','市场部',4000),
('赵红','技术部',2000),
('李四','市场部',5000),
('李白','技术部',5000),
('王五','市场部',NULL),
('王蓝','技术部',4000)
) as employ(name,dept,salary);
Oracle中有没有类似的方法创建常量临时表?PS:另外ORACLE中的INSERT INTO功能也不够强大,如DB2中可以这样来插入数据
insert into t_trans(cid,seq,input,amount) values
('zs001',1,'2000-1-1',350.00) ,
('zs001',2,'2000-1-2',140.00) ,
('ls002',3,'2000-1-3',220.00) ,
('zs001',4,'2000-1-4',270.00) ,
('ls002',5,'2000-1-5',480.00) ,
('ls002',6,'2000-1-6',660.00) ;
select
name,
salary,
lag(salary,0) over(order by salary) as lag0,
lag(salary) over(order by salary) as lag1,
lag(salary,2) over(order by salary) as lag2,
lag(salary,3,0,'IGNORE NULLS') over(order by salary) as lag3,
lag(salary,4,-1,'RESPECT NULLS') over(order by salary) as lag4,
lead(salary) over(order by salary) as lead
from (
values
('张三','市场部',4000),
('赵红','技术部',2000),
('李四','市场部',5000),
('李白','技术部',5000),
('王五','市场部',NULL),
('王蓝','技术部',4000)
) as employ(name,dept,salary);
Oracle中有没有类似的方法创建常量临时表?PS:另外ORACLE中的INSERT INTO功能也不够强大,如DB2中可以这样来插入数据
insert into t_trans(cid,seq,input,amount) values
('zs001',1,'2000-1-1',350.00) ,
('zs001',2,'2000-1-2',140.00) ,
('ls002',3,'2000-1-3',220.00) ,
('zs001',4,'2000-1-4',270.00) ,
('ls002',5,'2000-1-5',480.00) ,
('ls002',6,'2000-1-6',660.00) ;
1)你的需求1可以用with字句实现
2)在insert方面,Oracle更强,甚至可以向多个表发散弹枪
http://blog.csdn.net/linwaterbin/article/details/7994147
with t as (select 1 as nums from dual
union
select 2 as nums from dual
union
select 3 as nums from dual
)
select * from t where nums=1
使用WITH构建临时表经常用,但是使用UNION ALL来构建常量临时表的方法写起来太繁琐,呵呵!在演示一些SQL功能的时候创建表、插入数据、再删除表太麻烦,像问题中DB2的做法就很简单,我相信ORACLE中应该也有,只不过我不知道而已,期待有更精彩的回答,谢谢!
test -- test此时就是with构筑的临时表
as
(
select 1 from dual --这个就是临时表中的SQL
)
----此时可以直接把test拿来用,例如
select * from test 如果你要创多个临时表,可以这样
with
test1 as ( SQL文1),
test2 as (SQL 文2),
test3 as (SQL文3)
select * from test1,test2,test3
with tb1 as(
select '1' as str from dual
union all
select '2' as str from dual
)
多表
with tb1 as(
select 'A1' as str from dual
union all
select 'A2' as str from dual
),with tb2 as(
select 'B1' as str from dual
union all
select 'B2' as str from dual
)