参考:
http://search.csdn.net/expert/topic/61/6101/2003/3/19/1551178.htm
http://search.csdn.net/expert/topic/61/6101/2003/3/19/1551178.htm
解决方案 »
- 请问Oracle表中插入记录后如何返回自增主键值?
- oracle数据库的用户口令密文放在什么地方?
- 关于oracle英文字符集乱码的问题,查询显示乱码的问题。
- 如何得到ORACLE的某个序列的当前值
- oracle 10g 查询乱码
- 求Oracle10g参考手册的PDF电子版!!
- UPDATE语句的问题?
- 数据迁移 OLD SERVER DATA MOVE TO NEW SERVER
- 怎么获取数据库服务器的IP????????
- select如何设定若sum(字段name)统计后结果若为零则不输出?(只需要判断为零状态)
- 如何在sql/plus中把select语句查询显示出来的结果中的显示字段长度缩小。
- (急)简单问题:怎样实现ORACLE数据库中日期(DATE)数据类型的比较以及查询?
Query to generate a list of values from a child table (in CSV format) for every primary key value in a parent table.i.e.if parent table hasid
1
2
3and the child table hasid value_id
1 a
1 b
1 c
2 d
3 e
3 fthis query will outputid list of value_ids
1 a,b,c
2 d
3 e,f
The only issue I can think of is what happens if the list is longer than the varchar2 limit of 4000 bytes.Obviously this only works with 9i due to the SYS_CONNECT_BY_PATH : (.-- get the longest csv list of values for each id
select
id,
SUBSTR(MAX(list) KEEP (DENSE_RANK FIRST ORDER BY (lev) desc),2) as lov
from
(
-- create a tree and use sys_connect_by_path to create a csv list of values
select id , SYS_CONNECT_BY_PATH(value_id, ',') list, level lev
from
(
-- create a result set with a linked list column for every id so that we can create a hierarchical tree with the next query
select r.id, i.value_id, rownum prow,
LAG(rownum, 1) OVER (PARTITION BY i.id ORDER BY value_id) as connect_id
from parent_table r, child_table i
where r.id = i.id
) r
START WITH connect_id is null
CONNECT BY PRIOR prow=connect_id
)
group by id
order by id
Alan
不过,这个功能在哪有帮助。或说oracle的帮助怎么用?
id aa
1 0201,0302,1009
2 0405,
另一表B表有bb
id bb
1 0201
1 0302
2 0405
怎样判断某个id是否存在?
条件是如果B中对应id的bb值之合并等天A中aa的值,但不包括当天日期之后的.即如果当天在10月9日之前,则id = 1返回,但如果在这之后,则id = 1不返回
谢谢!
用游标取出B表中某ID的所有bb,用||连接,
用select语句取出A表中对应ID的aa,
做判断,执行下一步。