Oracle：关于connect by + group by

Oracle：关于connect by + group by

connect by + group by 对于表是否存在主键有不同的结果

用代码块功能插入代码，请勿粘贴截图。 不用代码块回答率下降 50%

1. 包含主键

drop table employeeskey;
create table employeeskey(emp_id number(4) primary key, name varchar(10), 
                       salary number(6), manager_id number(4));

insert into employeeskey values (   1, 'King',   10000, 0);
insert into employeeskey values (  11, 'Smith',   8000,    1);
insert into employeeskey values (  21, 'Jones',   9000,    1);
insert into employeeskey values ( 211, 'Brown',   7500,   21);
insert into employeeskey values ( 212, 'Adams',   6200,   21);
insert into employeeskey values (2111, 'White',   5000,  211);
insert into employeeskey values (  111, 'wine',   8000,    11);
insert into employeeskey values (  112, 'spring',   8000,    11);
insert into employeeskey values (  1111, 'spring',   8000,    111);

Select emp_id , count(emp_id) 
from employeeskey 
connect by prior manager_id = emp_id
group by emp_id;

2. 不包含主键

drop table employeesnokey;
create table employeesnokey(emp_id number(4), name varchar(10), 
                       salary number(6), manager_id number(4));

insert into employeesnokey values (   1, 'King',   10000, 0);
insert into employeesnokey values (  11, 'Smith',   8000,    1);
insert into employeesnokey values (  21, 'Jones',   9000,    1);
insert into employeesnokey values ( 211, 'Brown',   7500,   21);
insert into employeesnokey values ( 212, 'Adams',   6200,   21);
insert into employeesnokey values (2111, 'White',   5000,  211);
insert into employeesnokey values (  111, 'wine',   8000,    11);
insert into employeesnokey values (  112, 'spring',   8000,    11);
insert into employeesnokey values (  1111, 'spring',   8000,    111);

Select emp_id , count(emp_id) 
from employeesnokey 
connect by prior manager_id = emp_id
group by emp_id;

1.包含主键的结果为

2. 不包含主键的结果为

问题不在connect by，而是group by，Oracle对主键分组是无效的，Oracle并不会去执行group by子句，这点可以通过查看sql执行计划验证；
这里想对主键分组可以加上一个to_number():

Select to_number(emp_id), count(emp_id) 
from employeeskey 
connect by prior manager_id = emp_id
group by  to_number(emp_id);