急救！一个带条件的层次查询

lastwinner

数据库结构
1、TA
CREATE TABLE TA
(
  N_ID      NUMBER(10)                          NOT NULL,
  C_NAME    VARCHAR2(50 BYTE),
  N_SUP_ID  NUMBER(10),
  C_COLOR   VARCHAR2(10 BYTE)
)
/
ALTER TABLE TA ADD (
  PRIMARY KEY
(N_ID))
/
2、TCODE
CREATE TABLE TCODE
(
  C_DM    VARCHAR2(10),
  C_MC VARCHAR2(50)
)
/
ALTER TABLE TCODE ADD (
  PRIMARY KEY
(C_DM))
/
3、TACODE
create view TACODE as
SELECT
N_ID, C_NAME, N_SUP_ID, C_COLOR, nvl(C_MC,C_COLOR) C_COLOR_NAME,
prior C_NAME C_SUP_NAME   
FROM TA, TCODE
where TA.C_COLOR = TCODE.C_DM(+)
start with N_SUP_ID is null
connect by prior N_ID = N_SUP_ID
/

测试数据：
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR ) VALUES (
1, 'a', NULL, 'A');
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR ) VALUES (
2, 'b', NULL, 'B');
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR ) VALUES (
3, 'c', NULL, 'C');
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR ) VALUES (
4, 'ab', 1, 'D');
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR ) VALUES (
5, 'ca', 3, 'D');
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR ) VALUES (
6, 'abc', 4, 'A');
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR ) VALUES (
7, 'abd', 4, 'A');
INSERT INTO TA ( N_ID, C_NAME, N_SUP_ID, C_COLOR ) VALUES (
8, 'ac', 1, 'C');
COMMIT;

INSERT INTO TCODE ( C_DM, C_MC) VALUES (
'A', '黑色');
INSERT INTO TCODE ( C_DM, C_MC) VALUES (
'B', '红色');
INSERT INTO TCODE ( C_DM, C_MC) VALUES (
'C', '蓝色');
INSERT INTO TCODE ( C_DM, C_MC) VALUES (
'E', '灰色');
COMMIT;

看看视图中的数据：
select * from tacode;
1        a                A        黑色       
8        ac        1        C        蓝色        a
4        ab        1        D        D        a
7        abd        4        A        黑色        ab
6        abc        4        A        黑色        ab
2        b                B        红色       
3        c                C        蓝色       
5        ca        3        D        D        c

当我以C_COLOR为条件做查询时：
select  * from tacode
where c_color='C';
8        ac        1        C        蓝色        a
8        ac        1        C        蓝色        a
8        ac        1        C        蓝色        a
8        ac        1        C        蓝色        a
3        c                C        蓝色       
可我想要的结果是这样才对
3        c                C        蓝色       
8        ac        1        C        蓝色        a

请问这个查询语句应该怎么写？（只能通过TACODE去查）
以C_NAME、N_ID作为条件，都不会出现这种情况。

mychary

我的测试正常呀，你的版本是？我的是 10G

lastwinner

我也纳闷，刚才忘写版本了
是9.2.0.1

现在我把TACODE写成这样
select A.*,nvl(C_MC,C_COLOR) C_COLOR_NAME from
(
SELECT
N_ID, C_NAME, N_SUP_ID, C_COLOR,
prior C_NAME C_SUP_NAME   
FROM TA
start with N_SUP_ID is null
connect by prior N_ID = N_SUP_ID
) A, TCODE
where A.C_COLOR = TCODE.C_DM(+)

居然就没问题了
看了下执行计划，貌似现在这种写法效率还高
搞不懂，现在可是套了一层select的说
看来是oracle的bug了...

lastwinner

在816上测试了一下
创建视图时出错
SQL> create view TACODE as
  2  SELECT
  3  N_ID, C_NAME, N_SUP_ID, C_COLOR, nvl(C_MC,C_COLOR) C_COLOR_NAME,
  4  prior C_NAME C_SUP_NAME
  5  FROM TA, TCODE
  6  where TA.C_COLOR = TCODE.C_DM(+)
  7  start with N_SUP_ID is null
  8  connect by prior N_ID = N_SUP_ID
  9  /
FROM TA, TCODE
     *
ERROR 位于第 5 行:
ORA-01437: 无法连接 CONNECT BY

ft，816还不支持这种写法