求教 数据库 达人 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
最佳答案
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
基本思想能领会了，谢谢
Oracle 11g也支持递归CTE了。
