首页 新闻 赞助 找找看

求教 数据库 达人 SQL CTE

0
悬赏园豆:20 [已解决问题] 解决于 2010-07-20 11:42

求教 数据库 达人 Oracle 中用什么语法能实现 SQL 中CTE的效果

表 

ID、  ITEM_ID、 SUB_ITEM_ID 、ITEM_FEATURE、 SUB_ITEM_FEATURE

63bc9a80-81b6-4b43-a5fd-df2da8499cc6 A AB 11 22
63bc9a80-81b6-4b43-a5fd-df2da8499c26 A AC 11 22
63bc9a80-81b6-4b43-a5fd-df2da84993c6 AB ABC NULL 33
63bc9a80-81b6-4b43-a5fd-df2da8399cc6 AB ABD 22 44
63bc9a80-81b6-4b43-a5fd-df2da8469cc6 AC ACD 22 55
NULL NULL NULL NULL NULL

 MS SQL语句

代码
DECLARE @item nvarchar(50),
@feature nvarchar(50)
set @item='A'
SET @feature='11';
WITH TEST
AS
(
SELECT 0 AS LEV,CAST('' AS NVARCHAR(50)) AS ITEM_ID,@item AS SUB_ITEM_ID,CAST('' AS NVARCHAR(50))AS ITEM_FEATURE,@feature AS SUB_ITEM_FEATURE
UNION ALL
SELECT LEV+1,F.SUB_ITEM_ID AS ITEM_ID,B.SUB_ITEM_ID,B.ITEM_FEATURE ,B.SUB_ITEM_FEATURE
FROM TEST AS F JOIN TEST_ITEM AS B ON B.ITEM_ID=F.SUB_ITEM_ID AND (B.ITEM_FEATURE IS NULL OR B.ITEM_FEATURE=F.SUB_ITEM_FEATURE)
)
SELECT * FROM TEST

高手提供Oracle的SQL语句写法,谢谢

 

0A11
1AAB1122
1AAC1122
2ACACD2255
2ABABCNULL33
2ABABD2244

 

 

 

Yurnero的主页 Yurnero | 初学一级 | 园豆:125
提问于:2010-07-09 09:53
< >
分享
最佳答案
0
DECLARE @item nvarchar(50),
@feature nvarchar(50)
set @item='A'
SET @feature='11';
WITH TEST_ITEM as
(
select '63bc9a80-81b6-4b43-a5fd-df2da8499cc6' as id,
cast('A' as NVARCHAR(50)) as item_id,
cast('ABS' as NVARCHAR(50)) as SUB_ITEM_ID,
cast('11' as NVARCHAR(50)) as ITEM_FEATURE,
cast('22' as NVARCHAR(50)) as SUB_ITEM_FEATURE
union all
select '63bc9a80-81b6-4b43-a5fd-df2da8499c26','A','AC','11','22'
union all
select '63bc9a80-81b6-4b43-a5fd-df2da84993c6','AB','ABC',NULL,'33'
union all
select '63bc9a80-81b6-4b43-a5fd-df2da8399cc6','AB','ABD','22','44'
union all
select '63bc9a80-81b6-4b43-a5fd-df2da8469cc6','AC','ACD','22','55'
union all
select null,null,null,null,null
),
TEST
AS
(
SELECT 0 AS LEV, CAST('' AS NVARCHAR(50)) AS ITEM_ID,
@item AS SUB_ITEM_ID,CAST('' AS NVARCHAR(50)) AS ITEM_FEATURE,
@feature AS SUB_ITEM_FEATURE
UNION ALL
SELECT LEV+1, F.SUB_ITEM_ID AS ITEM_ID,
B.SUB_ITEM_ID, B.ITEM_FEATURE ,
B.SUB_ITEM_FEATURE
FROM TEST AS F
JOIN TEST_ITEM AS B
ON B.ITEM_ID=F.SUB_ITEM_ID
AND (B.ITEM_FEATURE IS NULL OR B.ITEM_FEATURE=F.SUB_ITEM_FEATURE)
)
SELECT * FROM TEST



LEV ITEM_ID SUB_ITEM_ID ITEM_FEATURE SUB_ITEM_FEATURE
---- -------- ------------ ------------- ----------------
0 A 11
1 A ABS 11 22
1 A AC 11 22
2 AC ACD 22 55

 

 

Oracle 的:

WITH TEST_ITEM as
(
select '63bc9a80-81b6-4b43-a5fd-df2da8499cc6' as id,
'A' as item_id,
'ABS' as SUB_ITEM_ID,
'11' as ITEM_FEATURE,
'22' as SUB_ITEM_FEATURE from dual
union all
select '63bc9a80-81b6-4b43-a5fd-df2da8499c26','A','AC','11','22' from dual
union all
select '63bc9a80-81b6-4b43-a5fd-df2da84993c6','AB','ABC',NULL,'33' from dual
union all
select '63bc9a80-81b6-4b43-a5fd-df2da8399cc6','AB','ABD','22','44' from dual
union all
select '63bc9a80-81b6-4b43-a5fd-df2da8469cc6','AC','ACD','22','55' from dual
union all
select null,null,null,null,null from dual
union all
select '','','A','','11' from dual
)
select level-1 lev,item_id,sub_item_id,item_feature,sub_item_feature
from TEST_ITEM
start
with sub_item_id='A' and sub_item_feature='11'
connect
by item_id= prior sub_item_id
and (item_feature is null or item_feature= prior sub_item_feature)


LEV IT SUB IT SU
---- -- --- -- --
0 A 11
1 A ABS 11 22
1 A AC 11 22
2 AC ACD 22 55

是不是你想要的结果啊

收获园豆:20
killkill | 小虾三级 |园豆:1192 | 2010-07-17 17:36
基本思想能领会了,谢谢
Yurnero | 园豆:125 (初学一级) | 2010-07-19 09:17
Oracle 11g也支持递归CTE了。
killkill | 园豆:1192 (小虾三级) | 2010-07-19 09:38
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册