1. with tt as(select '1号棒' 接力棒,'李一' 当前传递人,'李二' 下个接棒人 from dual union all select '1号棒' ,'李二' ,'李三' from dual union all select '1号棒' ,'李三' ,'李四' from dual)select 接力棒,count(1)+1 from tt t connect by 当前传递人=prior 下个接棒人 start with not exists(select 1 from tt where 下个接棒人=t.当前传递人 and 接力棒=t.接力棒) group by 接力棒
wildwave 大哥:还有问题2。和问题3呢。。好人做到底吧。。我正在消化你的代码。。
2.with tt as(select '1号棒' 接力棒,'李一' 当前传递人,'李二' 下个接棒人 from dual union all select '1号棒' ,'李二' ,'李三' from dual union all select '1号棒' ,'李三' ,'李四' from dual union all select '1号棒' ,'李四' ,'刘一' from dual union all select '2号棒' ,'刘一' ,'刘二' from dual union all select '2号棒' ,'刘三' ,'张一' from dual union all select '3号棒' ,'张一' ,'张二' from dual union all select '3号棒' ,'张二' ,'张三' from dual union all select '3号棒' ,'张三' ,'王一' from dual union all select '4号棒' ,'王一' ,'王五' from dual)
select count(1) from tt t connect by 当前传递人=prior 下个接棒人 start with 下个接棒人='张二'3. with tt as(select '1号棒' 接力棒,'李一' 当前传递人,'李二' 下个接棒人 from dual union all select '1号棒' ,'李二' ,'李三' from dual union all select '1号棒' ,'李三' ,'李四' from dual union all select '1号棒' ,'李四' ,'刘一' from dual union all select '2号棒' ,'刘一' ,'刘二' from dual union all select '2号棒' ,'刘三' ,'张一' from dual union all select '3号棒' ,'张一' ,'张二' from dual union all select '3号棒' ,'张二' ,'张三' from dual union all select '3号棒' ,'张三' ,'王一' from dual union all select '4号棒' ,'王一' ,'王五' from dual)
select count(distinct 接力棒) from tt t connect by 当前传递人=prior 下个接棒人 start with 当前传递人='张二'
2. with tt as(select '1号棒' 接力棒,'李一' 当前传递人,'李二' 下个接棒人 from dual union all select '1号棒' ,'李二' ,'李三' from dual union all select '1号棒' ,'李三' ,'李四' from dual union all select '2号棒' ,'李四' ,'王一' from dual union all select '2号棒' ,'王一' ,'王二' from dual union all select '2号棒' ,'王二' ,'王三' from dual) select count(1)+1 FROM ( select rownum rn,下个接棒人 from tt t connect by 当前传递人=prior 下个接棒人 start with 当前传递人='李一' order by rownum )t where rn<( select rn from ( select rownum rn,下个接棒人 from tt t connect by 当前传递人=prior 下个接棒人 start with 当前传递人='李一' order by rownum )t where 下个接棒人='王二')
SQLite3?这个是什么数据库,是MS SQL?如果不是oracle 那个start with, connecty by 就不能用了!只能想其他办法!
要自己定义下个接棒人的话 with tt as(select '1号棒' 接力棒,'李一' 当前传递人,'李二' 下个接棒人 from dual union all select '1号棒' ,'李二' ,'李三' from dual union all select '1号棒' ,'李三' ,'李四' from dual union all select '1号棒' ,'李四' ,'刘一' from dual union all select '2号棒' ,'刘一' ,'刘二' from dual union all select '2号棒' ,'刘三' ,'张一' from dual union all select '3号棒' ,'张一' ,'张二' from dual union all select '3号棒' ,'张二' ,'张三' from dual union all select '3号棒' ,'张三' ,'王一' from dual union all select '4号棒' ,'王一' ,'王五' from dual)
select abs(sum(l*power(-1,rownum)))+1 num from( select 当前传递人,下个接棒人,level l from tt t connect by 当前传递人=prior 下个接棒人 start with 当前传递人='张二') where 当前传递人='张二' or 下个接棒人='王五' 或 with tt as(select '1号棒' 接力棒,'李一' 当前传递人,'李二' 下个接棒人 from dual union all select '1号棒' ,'李二' ,'李三' from dual union all select '1号棒' ,'李三' ,'李四' from dual union all select '1号棒' ,'李四' ,'刘一' from dual union all select '2号棒' ,'刘一' ,'刘二' from dual union all select '2号棒' ,'刘三' ,'张一' from dual union all select '3号棒' ,'张一' ,'张二' from dual union all select '3号棒' ,'张二' ,'张三' from dual union all select '3号棒' ,'张三' ,'王一' from dual union all select '4号棒' ,'王一' ,'王五' from dual)
select count(1) from( select 当前传递人,下个接棒人,level l from tt t connect by 当前传递人=prior 下个接棒人 and prior 当前传递人<>'王五' start with 当前传递人='张二') 或with tt as(select '1号棒' 接力棒,'李一' 当前传递人,'李二' 下个接棒人 from dual union all select '1号棒' ,'李二' ,'李三' from dual union all select '1号棒' ,'李三' ,'李四' from dual union all select '1号棒' ,'李四' ,'刘一' from dual union all select '2号棒' ,'刘一' ,'刘二' from dual union all select '2号棒' ,'刘二' ,'刘三' from dual union all select '2号棒' ,'刘三' ,'张一' from dual union all select '3号棒' ,'张一' ,'张二' from dual union all select '3号棒' ,'张二' ,'张三' from dual union all select '3号棒' ,'张三' ,'王一' from dual union all select '4号棒' ,'王一' ,'王五' from dual)
,c as(select 当前传递人,下个接棒人,level l from tt t connect by 当前传递人=prior 下个接棒人 start with not exists(select 1 from tt where 下个接棒人=t.当前传递人) ) select b.l-a.l+1 from (select * from c where 当前传递人='张二')a,(select * from c where 下个接棒人='王五')b
sqlite不会用,所以不知道怎么写能执行 connect by在非oracle的环境应该是没法运行的,不过你可以写个函数来实现 比如 create or replace function func(str1 in varchar2,str2 in varchar2)return number as v_name varchar2(10):=str1; num number:=0; begin loop select 下个接棒人 into v_name from tt where 当前传递人=v_name; num:=num+1; exit when v_name=str2; end loop; return num; exception when others then return 0; end func; 然后调用 select func('张二','王五') from dual这样写的话要注意语法问题,这个是按oracle的语法写的
union all select '1号棒' ,'李二' ,'李三' from dual
union all select '1号棒' ,'李三' ,'李四' from dual)select 接力棒,count(1)+1 from tt t
connect by 当前传递人=prior 下个接棒人
start with not exists(select 1 from tt where 下个接棒人=t.当前传递人 and 接力棒=t.接力棒)
group by 接力棒
union all select '1号棒' ,'李二' ,'李三' from dual
union all select '1号棒' ,'李三' ,'李四' from dual
union all select '1号棒' ,'李四' ,'刘一' from dual
union all select '2号棒' ,'刘一' ,'刘二' from dual
union all select '2号棒' ,'刘三' ,'张一' from dual
union all select '3号棒' ,'张一' ,'张二' from dual
union all select '3号棒' ,'张二' ,'张三' from dual
union all select '3号棒' ,'张三' ,'王一' from dual
union all select '4号棒' ,'王一' ,'王五' from dual)
select count(1) from tt t
connect by 当前传递人=prior 下个接棒人
start with 下个接棒人='张二'3.
with tt as(select '1号棒' 接力棒,'李一' 当前传递人,'李二' 下个接棒人 from dual
union all select '1号棒' ,'李二' ,'李三' from dual
union all select '1号棒' ,'李三' ,'李四' from dual
union all select '1号棒' ,'李四' ,'刘一' from dual
union all select '2号棒' ,'刘一' ,'刘二' from dual
union all select '2号棒' ,'刘三' ,'张一' from dual
union all select '3号棒' ,'张一' ,'张二' from dual
union all select '3号棒' ,'张二' ,'张三' from dual
union all select '3号棒' ,'张三' ,'王一' from dual
union all select '4号棒' ,'王一' ,'王五' from dual)
select count(distinct 接力棒) from tt t
connect by 当前传递人=prior 下个接棒人
start with 当前传递人='张二'
with tt as(select '1号棒' 接力棒,'李一' 当前传递人,'李二' 下个接棒人 from dual
union all select '1号棒' ,'李二' ,'李三' from dual
union all select '1号棒' ,'李三' ,'李四' from dual
union all select '2号棒' ,'李四' ,'王一' from dual
union all select '2号棒' ,'王一' ,'王二' from dual
union all select '2号棒' ,'王二' ,'王三' from dual)
select count(1)+1 FROM
(
select rownum rn,下个接棒人 from tt t
connect by 当前传递人=prior 下个接棒人
start with 当前传递人='李一'
order by rownum
)t
where rn<(
select rn from ( select rownum rn,下个接棒人 from tt t
connect by 当前传递人=prior 下个接棒人
start with 当前传递人='李一'
order by rownum
)t where 下个接棒人='王二')
一个procedure负责执行查找,调用function
with tt as(select '1号棒' 接力棒,'李一' 当前传递人,'李二' 下个接棒人 from dual
union all select '1号棒' ,'李二' ,'李三' from dual
union all select '1号棒' ,'李三' ,'李四' from dual
union all select '1号棒' ,'李四' ,'刘一' from dual
union all select '2号棒' ,'刘一' ,'刘二' from dual
union all select '2号棒' ,'刘三' ,'张一' from dual
union all select '3号棒' ,'张一' ,'张二' from dual
union all select '3号棒' ,'张二' ,'张三' from dual
union all select '3号棒' ,'张三' ,'王一' from dual
union all select '4号棒' ,'王一' ,'王五' from dual)
select abs(sum(l*power(-1,rownum)))+1 num from(
select 当前传递人,下个接棒人,level l from tt t
connect by 当前传递人=prior 下个接棒人
start with 当前传递人='张二')
where 当前传递人='张二' or 下个接棒人='王五'
或
with tt as(select '1号棒' 接力棒,'李一' 当前传递人,'李二' 下个接棒人 from dual
union all select '1号棒' ,'李二' ,'李三' from dual
union all select '1号棒' ,'李三' ,'李四' from dual
union all select '1号棒' ,'李四' ,'刘一' from dual
union all select '2号棒' ,'刘一' ,'刘二' from dual
union all select '2号棒' ,'刘三' ,'张一' from dual
union all select '3号棒' ,'张一' ,'张二' from dual
union all select '3号棒' ,'张二' ,'张三' from dual
union all select '3号棒' ,'张三' ,'王一' from dual
union all select '4号棒' ,'王一' ,'王五' from dual)
select count(1) from(
select 当前传递人,下个接棒人,level l from tt t
connect by 当前传递人=prior 下个接棒人 and prior 当前传递人<>'王五'
start with 当前传递人='张二')
或with tt as(select '1号棒' 接力棒,'李一' 当前传递人,'李二' 下个接棒人 from dual
union all select '1号棒' ,'李二' ,'李三' from dual
union all select '1号棒' ,'李三' ,'李四' from dual
union all select '1号棒' ,'李四' ,'刘一' from dual
union all select '2号棒' ,'刘一' ,'刘二' from dual
union all select '2号棒' ,'刘二' ,'刘三' from dual
union all select '2号棒' ,'刘三' ,'张一' from dual
union all select '3号棒' ,'张一' ,'张二' from dual
union all select '3号棒' ,'张二' ,'张三' from dual
union all select '3号棒' ,'张三' ,'王一' from dual
union all select '4号棒' ,'王一' ,'王五' from dual)
,c as(select 当前传递人,下个接棒人,level l from tt t
connect by 当前传递人=prior 下个接棒人
start with not exists(select 1 from tt where 下个接棒人=t.当前传递人)
)
select b.l-a.l+1 from (select * from c where 当前传递人='张二')a,(select * from c where 下个接棒人='王五')b
connect by在非oracle的环境应该是没法运行的,不过你可以写个函数来实现
比如
create or replace function func(str1 in varchar2,str2 in varchar2)return number
as
v_name varchar2(10):=str1;
num number:=0;
begin
loop
select 下个接棒人 into v_name from tt where 当前传递人=v_name;
num:=num+1;
exit when v_name=str2;
end loop;
return num;
exception
when others then
return 0;
end func;
然后调用
select func('张二','王五') from dual这样写的话要注意语法问题,这个是按oracle的语法写的