- 浏览: 563063 次
- 性别:
- 来自: 大连
文章分类
最新评论
-
sucheng2016:
最近也遇到同樣的問題, 我用的是com.fasterxml.j ...
Java的Timezone问题 -
netwelfare:
这里有篇《时间纪元与时区介绍》,讲解的不错,可以看看。
Java的Timezone问题 -
yjplxq:
...
Java -jar 选项与 -cp/-classpath -
phil09s:
问题是,为什么要设定成这样?
Java局部变量必须初始化 -
anttu:
...
db2 SQL: value(), values(), with, recursive SQL
comment:
-- comment should be on a separate line outside an SQL statement
select 1 from (values 1) as aa -- this is not a valid db2 comment
way to create simple tests against dynamic table:
select 'a' as col1,'b' as col2 from table (values 1) as dummy
or shorter:
select 'a','b' from (values 1) qq
In fact, for simple tests you don't even need select and a dummy table.
values ( 'mama', 1)
values (cast (2.5 as decimal(10,3)) * cast (2.5 as decimal(10,3)) )
using cast( ) and values( ), building null values on the fly:
select
'abc' as col1,
cast(null as varchar(80)) as col2
from
table (values (1,2),(3,4)) as dummy
Hint how to make running tests from the prompt easy:
I create 2 aliases:
alias vv='vi test.sql'
alias rr='db2 -tvf test.sql'
Type 'vv' to edit test.sql file (remember - you should end each SQL statement with a semicolon ';')
Then type 'rr' to run this SQL.
simple insert:
insert into inst1.test (A) values ('a'), ('b'), ('c')
using case:
select
case
when 1<2 then 'mama'
when 1>2 then 'papa'
end
as person
from table (values 1) as qq
concatenating:
select 'mama' concat ' papa' from table (values 1) as qq;
select 'mama' || ' papa' from table (values 1) as qq;
union, intersect & except:
select .. from T1 union T2 - remove duplicates
select .. from T1 union all T2 -- preserve duplicates
select .. from T1 intersect T2 - remove duplicates
select .. from T1 intersect all T2 -- contains min number of repetition
select .. from T1 except T2 - remove duplicates, then do except
select .. from T1 except all T2 do except, then remove duplicates
value( ) function:
-- value() function - accepts a variable number of parameteres and returns a
first non-null value
-- parameters should be of compatible types
-- the actual name for this function is coalesce() - means "to arise from a
combination of distinct elements"
select value(1,2) from (values 1) as aa
-- returns 1
select value(cast(null as int),2) from (values 1) as aa
-- returns 2
select value(cast(null as int),cast(null as int),3) from (values 1) as aa
-- returns 3
------------------------------------------------------------
2 ways (common table and inline table expression) to define table dynamically in the SQL statement:
with tree (id,pid) as (
values (1,2), (2,3), (3,4), (4, cast(null as int)), (5,3), (6,5)
)
select * from tree
ID PID
1 2
2 3
3 4
4 [NULL]
5 3
6 5
-------------------------------------------- another way to do the same
select * from (
values (1,2), (2,3), (3,4), (4, cast(null as int)), (5,3), (6,5)
)
as a
Getting root of the tree using recursive SQL:
with
tree (id,pid) as (
values (1,2), (2,3), (3,4), (4, cast(null as int)), (5,3), (6,5)
),
rr (id,pid) as (
select tr.id, tr.pid from tree tr where tr.id=1
union all
select tt.id,tt.pid from tree tt, rr
where tt.id = rr.pid
)
select id from rr where rr.pid is null
More recursive SQL:
We have table storing a tree (each row has id and parent_id).
To get root id for a given id (12345) we fill out temporary table rr:
first with given id and its parent_id. Then union with recursively calculated
id and parent_id as we go up the tree:
with rr (parent_id, id) as
(
select p.parent_id, p.id
from mytree p
where p.id = 12345
union all
select p.parent_id, p.id
from mytree p, rr
where p.id = rr.parent_id
)
select id ROOT from rr where parent_id is null;
Here is an example going in the oposite direction (down the tree). We showing the whole tree under a given id:
with rr (id, level) as
(
select id, 1
from mytree
where id = 10001
union all
select child.id, parent.level + 1
from mytree child, rr parent
where parent.id = child.parent_id
)
select * from rr;
Result:
ID LEVEL
----------------------------
10001 1
29361 2
23044 3
25162 3
25302 3
Here is how to combine 2 above queries together:
----------------------------------------------------------------------------
-- Given any node in any tree, this query will drill up to the top
-- level of the tree, and then query down to give all nodes that exist
-- within the tree (i.e. given any node_id, show the whole
-- tree that contains it).
--
-- Note: This query will never go deeper than 'stop_level' levels (10).
----------------------------------------------------------------------------
with rec_root (parent_id, child_id, sub_query, level, stop_level) as
(
select my_parent_id, my_id, 1, 0, 0
from mydb.mytable
where my_id = 25162
union all
select parent.my_parent_id, parent.my_id, 2, 1, child.level + 1
from mydb.mytable parent, rec_root child
where parent.my_id = child.parent_id
and sub_query in (1,2)
and child.stop_level < 10
union all
select my_parent_id, my_id, 3, parent3.level + 1, parent3.level + 1
from rec_root parent3, mydb.mytable child3
where parent3.child_id = child3.my_parent_id
and ( (parent3.sub_query = 2 and parent3.parent_id is null)
or parent3.sub_query = 3
)
and parent3.stop_level < 10
)
select * from rec_root
where parent_id is null
or sub_query = 3;
-- comment should be on a separate line outside an SQL statement
select 1 from (values 1) as aa -- this is not a valid db2 comment
way to create simple tests against dynamic table:
select 'a' as col1,'b' as col2 from table (values 1) as dummy
or shorter:
select 'a','b' from (values 1) qq
In fact, for simple tests you don't even need select and a dummy table.
values ( 'mama', 1)
values (cast (2.5 as decimal(10,3)) * cast (2.5 as decimal(10,3)) )
using cast( ) and values( ), building null values on the fly:
select
'abc' as col1,
cast(null as varchar(80)) as col2
from
table (values (1,2),(3,4)) as dummy
Hint how to make running tests from the prompt easy:
I create 2 aliases:
alias vv='vi test.sql'
alias rr='db2 -tvf test.sql'
Type 'vv' to edit test.sql file (remember - you should end each SQL statement with a semicolon ';')
Then type 'rr' to run this SQL.
simple insert:
insert into inst1.test (A) values ('a'), ('b'), ('c')
using case:
select
case
when 1<2 then 'mama'
when 1>2 then 'papa'
end
as person
from table (values 1) as qq
concatenating:
select 'mama' concat ' papa' from table (values 1) as qq;
select 'mama' || ' papa' from table (values 1) as qq;
union, intersect & except:
select .. from T1 union T2 - remove duplicates
select .. from T1 union all T2 -- preserve duplicates
select .. from T1 intersect T2 - remove duplicates
select .. from T1 intersect all T2 -- contains min number of repetition
select .. from T1 except T2 - remove duplicates, then do except
select .. from T1 except all T2 do except, then remove duplicates
value( ) function:
-- value() function - accepts a variable number of parameteres and returns a
first non-null value
-- parameters should be of compatible types
-- the actual name for this function is coalesce() - means "to arise from a
combination of distinct elements"
select value(1,2) from (values 1) as aa
-- returns 1
select value(cast(null as int),2) from (values 1) as aa
-- returns 2
select value(cast(null as int),cast(null as int),3) from (values 1) as aa
-- returns 3
------------------------------------------------------------
2 ways (common table and inline table expression) to define table dynamically in the SQL statement:
with tree (id,pid) as (
values (1,2), (2,3), (3,4), (4, cast(null as int)), (5,3), (6,5)
)
select * from tree
ID PID
1 2
2 3
3 4
4 [NULL]
5 3
6 5
-------------------------------------------- another way to do the same
select * from (
values (1,2), (2,3), (3,4), (4, cast(null as int)), (5,3), (6,5)
)
as a
Getting root of the tree using recursive SQL:
with
tree (id,pid) as (
values (1,2), (2,3), (3,4), (4, cast(null as int)), (5,3), (6,5)
),
rr (id,pid) as (
select tr.id, tr.pid from tree tr where tr.id=1
union all
select tt.id,tt.pid from tree tt, rr
where tt.id = rr.pid
)
select id from rr where rr.pid is null
More recursive SQL:
We have table storing a tree (each row has id and parent_id).
To get root id for a given id (12345) we fill out temporary table rr:
first with given id and its parent_id. Then union with recursively calculated
id and parent_id as we go up the tree:
with rr (parent_id, id) as
(
select p.parent_id, p.id
from mytree p
where p.id = 12345
union all
select p.parent_id, p.id
from mytree p, rr
where p.id = rr.parent_id
)
select id ROOT from rr where parent_id is null;
Here is an example going in the oposite direction (down the tree). We showing the whole tree under a given id:
with rr (id, level) as
(
select id, 1
from mytree
where id = 10001
union all
select child.id, parent.level + 1
from mytree child, rr parent
where parent.id = child.parent_id
)
select * from rr;
Result:
ID LEVEL
----------------------------
10001 1
29361 2
23044 3
25162 3
25302 3
Here is how to combine 2 above queries together:
----------------------------------------------------------------------------
-- Given any node in any tree, this query will drill up to the top
-- level of the tree, and then query down to give all nodes that exist
-- within the tree (i.e. given any node_id, show the whole
-- tree that contains it).
--
-- Note: This query will never go deeper than 'stop_level' levels (10).
----------------------------------------------------------------------------
with rec_root (parent_id, child_id, sub_query, level, stop_level) as
(
select my_parent_id, my_id, 1, 0, 0
from mydb.mytable
where my_id = 25162
union all
select parent.my_parent_id, parent.my_id, 2, 1, child.level + 1
from mydb.mytable parent, rec_root child
where parent.my_id = child.parent_id
and sub_query in (1,2)
and child.stop_level < 10
union all
select my_parent_id, my_id, 3, parent3.level + 1, parent3.level + 1
from rec_root parent3, mydb.mytable child3
where parent3.child_id = child3.my_parent_id
and ( (parent3.sub_query = 2 and parent3.parent_id is null)
or parent3.sub_query = 3
)
and parent3.stop_level < 10
)
select * from rec_root
where parent_id is null
or sub_query = 3;
发表评论
-
DB2 9.5 SQL Procedure Developer 认证考试 735 准备
2011-06-23 23:45 1205DB2 9.5 SQL Procedure Developer ... -
DB2利用syscat.references递归查出他的所有关联表
2011-06-22 23:50 2438找出所有的父表: With reftables(refta ... -
DB2 9 应用开发(733 考试)认证指南
2011-05-25 14:12 1118DB2 9 应用开发(733 考试)认证指南 DB2 9 应 ... -
DB2 9 数据库管理(731考试)认证指南
2011-05-25 13:28 1199DB2 9 数据库管理(731 考试)认证指南 DB2 9 ... -
DB2 9 基础(730 考试)认证指南
2011-05-22 23:58 1219DB2 9 基础(730 考试)认证指南 DB2 9 基础 ... -
DB2 的CHECK不检查NULL值
2011-05-18 22:36 1283Create table test.testchk( c ... -
DB2创建VIEW的时候CHECK OPTION的作用
2011-05-11 22:48 3627创建视图的时候有几种CHECK OPTION CHECK ... -
让DB2自动更新统计信息以及设定资源使用限制
2011-05-07 00:20 4398刚接触DB2的时候遇到一个统计表占用空间问题,因为数据是从sy ... -
DB2如何暂时关闭外键约束
2011-05-06 23:32 2782迁移数据的时候一定遇到过导入导出的外键约束报错问题,外键约束是 ... -
DB2 extents 怎么计算
2011-04-29 22:44 768Hi group, I am going through s ... -
联邦数据库的一个例子!
2010-11-15 23:28 1862转载自:http://bbs.51cto.com/thread ... -
DB2 数据库性能调优十条
2010-11-04 23:22 4864DB2性能调整的10个技巧 ... -
DB2 监控死锁 db2evmon
2010-10-15 22:41 1592db2evmon -db db_name -evm db2de ... -
DB2 SQL3089N 错误解决
2010-10-06 22:29 2897SQL3089N A non-D record was ... -
DB2查看刚刚执行的SQL
2010-09-08 23:03 2993有时候我们需要查看数据库中正在执行那些SQL,以解决一些问题, ... -
DB2 BLOB 字段读取 SQL0423N 错误
2010-09-08 13:40 1810出现这种问题的解决办法有两种: 1.在JDBC中获取数据库连接 ... -
大家帮忙做做实验:like的时候‘%’的位置和是否索引扫描的关系
2010-05-13 14:31 1189以前有个人问过我一个问题,在查询的时候百分号的位置跟是否进行索 ... -
DB2 命令执行参数(command options)
2010-05-01 22:58 4782进入的db2命令行处理器: db2cmd 命令的参数可以控制 ... -
Oracle文档大全
2010-04-30 10:36 1108http://www.oracle.com/technolog ... -
DB2 导出自定义分隔符的文件
2010-04-07 13:51 10259本来想在Excel中另存一下就搞定这个问题,可是找了半天没有找 ...
相关推荐
postgre sql recursive sql. 在postgoresql 中使用recursive的脚本实现循环查询结果
使用标准 DH 参数描述的开放运动链的递归 Newton-Euler 逆动力学可选的: 机器人工具箱用于比较: http : //www.petercorke.com/RTB/ 下载并解压缩文件。 使用“pathtool”命令将路径添加到 MATLAB。...
You'll find many examples that address the language's complexity, along with key aspects of SQL used in IBM DB2 Release 9.7, MySQL 5.1, Oracle Database 11g Release 2, PostgreSQL 9.0, and Microsoft ...
6)DetectoRS: Detecting Objects with Recursive Feature; 7)YOLOv6: A Single-Stage Object Detection Framework for Industrial Applications; 8)YOLOv7: Trainable bag-of-freebies sets new state-of-the-art ...
重温 Iso-Recursive 子类型化(工件)抽象的这个包包含与论文“Revisiting Iso-Recursive Subtyping”相关的 Coq 公式。 本文档解释了如何运行 Coq 公式。入门我们强烈建议您通过opam2安装 Coq 证明助手。 安装 (>=...
12 ORA‐00604: error occurred at recursive SQL level 2 13 ORA‐01555: snapshot too old: rollback segment number 7 with name 14 "_SYSSMU7_4222772309$" too small 15 Process ID: 1730 16 Session ID: 1996 ...
Delve into the internal architecture of T-SQL—and unveil the power of set-based querying—with comprehensive reference and advice from a highly regarded T-SQL expert and members of Microsoft's SQL ...
Orthogonal matching pursuit: recursive function approximation with applications to wavelet decomposition
WinMerge can be compiled with: - Visual Studio 2005 or later - GCC 4 - Qt 4.5 or later WinMerge uses Qt's qmake to manage projects. To create makefiles you'll need to run command: > qmake -recursive...
用myeclispe来连接oracle数据库时出现了如下错误: ORA-00604: error occurred at recursive SQL level 1 ORA-12705: Cannot access NLS data files or invalid environment specified
Parsing with Perl 6 Regexes and Grammars A Recursive Descent into Parsing 英文无水印原版pdf pdf所有页面使用FoxitReader、PDF-XChangeViewer、SumatraPDF和Firefox测试都可以打开 本资源转载自网络,如有...
在这个模拟中,我使用递归最小二乘 (RLS) 和最小均方 (LMS) 算法实现了用于系统识别的代码。
Fast Multi-exposure Image Fusion with Median Filter and Recursive Filter, http://blog.sciencenet.cn/blog-366840-709637.html
火星 中号athematically-一个ugmented [R ecursive小号tylesheet是(目前理论)CSS预处理语言,企图以直观的那些谁已经知道CSS3。 请参阅包含的example.html (或 )以获取有关如何工作的语法高亮演示。
判断SQL语句是否与共享内存中某一SQL相同Rowid ,Recursive SQL, Row Source ,Predicate Driving Table ,Probed Table等概念4种类型的索引扫描
Introduction to recursive programming
For more information about how to decode this value, see also… Inside SQL Server 2000, pages 803 and 806. Key Range Locking Key Range Locking To support SERIALIZABLE transaction semantics, ...
Modified Extended Recursive Least Square with Exponential Forgetting(输出噪声)是一种参数估计方法,用于在系统参数随时间变化的情况下估计传递函数的参数,也估计应用于系统的噪声特性输出参考:Astrom 的...