参考:
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
解决方案 »
- oralce如何导出某个用户以及权限??
- orcale存储工程提示错误
- 如何使用oracle游标遍历select集合
- (急切求助)PL/SQL连接Oracle 9i问题。。。。
- 【求助】过程看不懂
- oracle的两表连接怎么实现,原来是SQL的现在想换成oracle的
- 如何建自动递增的字段呀? 晕! (ORACLE初学者)
- 有SQL语句查看表结构吗?
- 急急急。老大们救命呀!关于ORACLE回滚的,想让存储过程回滚。
- 请问哪里有developer 2000下载
- 如何在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,
做判断,执行下一步。