34,588
社区成员
发帖
与我相关
我的任务
分享
create table b(ke varchar(5),dan varchar(5))
insert b
select 'a', 'b'
union all select 'b', 'a'
union all select 'c', 'd'
union all select 'd', 'e'
union all select 'e', 'c'
select
b1.ke,
b2.ke,
b3.ke
from
b b1,
b b2,
b b3
where
b1.dan =b2.ke
and b2.dan<>b1.ke
and b2.dan=b3.ke
and b3.dan<>b2.ke
and b1.ke>b2.ke
----结果
e c d
--三级循环担保
select a.担保人,b.担保人,c.担保人
from @T a
join @T b on a.客户=b.担保人 and a.担保人<>b.客户
join @T c on b.客户=c.担保人 and b.担保人<>c.客户 and a.担保人=c.客户
--为更严谨,增加一个条件:b.担保人<>c.客户
where a.担保人<b.担保人
--四级循环担保
select a.担保人,b.担保人,c.担保人,d.担保人
from @T a
join @T b on a.客户=b.担保人 and a.担保人<>b.客户
join @T c on b.客户=c.担保人 and b.担保人<>c.客户
join @T d on c.客户=d.担保人 and c.担保人<>d.客户 and a.担保人=d.客户
--为更严谨,增加一个条件:c.担保人<>d.客户
where a.担保人<b.担保人
/*
就是不可能通过类似A>B的方式解决这个问题
--------------------
看如何写代码了,下面测试结果,如果不加WHERE部分,返回的是一个矩阵,那就完全可以通过 WHERE A<B 的方式解决问题。
*/
--测试数据:@T
declare @T table(客户 varchar(10),担保人 varchar(10))
insert @T
select 'a','b' union all
select 'b','a' union all
select 'c','d' union all
select 'd','e' union all
select 'e','c' union all
select '1','2' union all
select '2','1' union all
select '3','4' union all
select '4','5' union all
select '5','3'
union all
select '11','22' union all
select '22','33' union all
select '33','44' union all
select '44','11'
--二级循环担保
select a.担保人,b.担保人
from @T a
join @T b on a.客户=b.担保人 and a.担保人=b.客户
where a.担保人<b.担保人
/*
a b
1 2
*/
--三级循环担保
select a.担保人,b.担保人,c.担保人
from @T a
join @T b on a.客户=b.担保人 and a.担保人<>b.客户
join @T c on b.客户=c.担保人 and a.担保人=c.客户
where a.担保人<b.担保人
/*
c e d
3 5 4
*/
--四级循环担保
select a.担保人,b.担保人,c.担保人,d.担保人
from @T a
join @T b on a.客户=b.担保人 and a.担保人<>b.客户
join @T c on b.客户=c.担保人 and b.担保人<>c.客户
join @T d on c.客户=d.担保人 and a.担保人=d.客户
where a.担保人<b.担保人
/*
11 44 33 22
*/
create table b(ke varchar(5),dan varchar(5))
insert b
select 'a', 'b'
union all select 'b', 'a'
union all select 'c', 'd'
union all select 'd', 'e'
union all select 'e', 'c'
select
b1.ke,
b2.ke,
b3.ke
from
b b1,
b b2,
b b3
where
b1.dan =b2.ke
and b2.dan<>b1.ke
and b2.dan=b3.ke
and b3.dan<>b2.ke
and b1.ke>b2.ke
create table b(ke varchar(5),dan varchar(5))
insert b
select 'a', 'b'
union all select 'b', 'a'
union all select 'c', 'd'
union all select 'd', 'e'
union all select 'e', 'c'
select
b1.ke,
b2.ke,
b3.ke
from
b b1,
b b2,
b b3
where
b1.dan =b2.ke
and b2.dan<>b1.ke
and b2.dan=b3.ke
and b3.dan<>b2.ke
and b3.dan<>b1.ke
create table b(ke varchar(5),dan varchar(5))
insert b
select 'a', 'b'
union all select 'b', 'a'
union all select 'c', 'd'
union all select 'd', 'e'
union all select 'e', 'c'
select
b1.ke,
b2.ke,
b3.ke
from
b b1,
b b2,
b b3
where
b1.dan =b2.ke
and b2.dan<>b1.ke
and b2.dan=b3.ke
and b3.dan<>b2.ke
and b1.ke>b2.ke
select
b1.ke,
b2.ke
from
b b1,
b b2
where
b1.dan =b2.ke
and b2.dan=b1.ke
select b1.ke,b2.ke,b3.ke from b b1, b b2, b b3
where b1.dan=b2.ke and b2.dan=b3.ke and b1.ke>b2.ke and b1.ke>b3.ke
select
b1.ke,
b2.ke,
b3.ke
from
b b1,
b b2,
b b3
where
b1.dan =b2.ke
and b2.dan<>b1.ke
and b2.dan=b3.ke
and b3.dan<>b2.ke
and b1.ke>b2.ke