CREATE TABLE test ( id VARCHAR2(10), pid VARCHAR2(10), TYPE VARCHAR2(10) )INSERT INTO test VALUES('1',NULL,'0'); INSERT INTO test VALUES('2','1','1'); INSERT INTO test VALUES('3','1','1'); INSERT INTO test VALUES('4','2','2'); INSERT INTO test VALUES('5','3','2'); INSERT INTO test VALUES('6','4','2'); INSERT INTO test VALUES('7','4','2');SELECT * FROM test;SELECT TYPE,Count(DISTINCT pid),Count(DISTINCT id) FROM TEST t CONNECT BY PRIOR t.pid = t.TYPE AND t.id=t.pid GROUP BY TYPE; TYPE COUNT(DISTINCTPID) COUNT(DISTINCTID) 0 0 1 1 1 2 2 3 4要的是這個結果嗎?
id VARCHAR2(10),
pid VARCHAR2(10),
TYPE VARCHAR2(10)
)INSERT INTO test VALUES('1',NULL,'0');
INSERT INTO test VALUES('2','1','1');
INSERT INTO test VALUES('3','1','1');
INSERT INTO test VALUES('4','2','2');
INSERT INTO test VALUES('5','3','2');
INSERT INTO test VALUES('6','4','2');
INSERT INTO test VALUES('7','4','2');SELECT * FROM test;SELECT TYPE,Count(DISTINCT pid),Count(DISTINCT id) FROM TEST t CONNECT BY PRIOR t.pid = t.TYPE AND t.id=t.pid GROUP BY TYPE; TYPE COUNT(DISTINCTPID) COUNT(DISTINCTID)
0 0 1
1 1 2
2 3 4要的是這個結果嗎?