22,209
社区成员
发帖
与我相关
我的任务
分享
select 'A' as c1,'A' as c2 union all
select 'A' as c1,'B' as c2 union all
select 'B' as c1,'C' as c2 union all
select 'B' as c1,'D' as c2 union all
select 'E' as c1,'F' as c2 union all
select 'G' as c1,'H' as c2 union all
select 'G' as c1,'F' as c2 union all
select 'J' as c1,'D' as c2 union all
select 'K' as c1,'C' as c2
这个关系表里的最后添加A和G的关系。
如下,
select 'A' as c1,'A' as c2 union all
select 'A' as c1,'B' as c2 union all
select 'B' as c1,'C' as c2 union all
select 'B' as c1,'D' as c2 union all
select 'E' as c1,'F' as c2 union all
select 'G' as c1,'H' as c2 union all
select 'G' as c1,'F' as c2 union all
select 'J' as c1,'D' as c2 union all
select 'K' as c1,'C' as c2 union all
select 'A' as c1,'G' as c2
然后你可分析一下C1和C2之间的关系。
A A →A和A,是本身。
A B →A和B,说明,AB有关系。
B C →B和C,说明,ABC有关系。
B D →B和D,说明,ABCD有关系。
E F →E和F,说明,EF有关系。
G H →G和H,说明,GH有关系。
G F →G和F,说明,EFGH有关系。
J D →J和D,说明,ABCDJ有关系。
K C →K和C,说明,ABCDJK有关系。
所以,分为2组。
到这里,应该认识都一样把。
那么,在最后一条,加上A和G,换句话说,ABCDEFGHJK都有关系。只有1组。
而,以上的SQL语句,只针对原来数据进行组合加工,没有考虑到在之后添加条件,这样,这个SQL就是BUG。
并不是从根据答案去写SQL,而应该是先要把他们的关系整理好之后,再写。这是我认为的。
我看,这样的问题,不写循环游标,可能无法实现。
我自己也写了一段,可以正对最后的A,G关系。
但是,我把A,G关系删掉之后,会有BUG,Oh,MyGod。
with main as (
select * from
(select ColA,ColB from RELATION
union
select ColB,ColA from RELATION) as A
)
,sub as (
select ROW_NUMBER() over(order by sort,ColA) no ,* from main
Cross Apply(
select min(ColB) sort from main as sub
where main.ColA = sub.ColA
group by ColA
) as B
)
, sub2 as (
select sub.no,sub.ColA,sub.ColB,isnull(B.sort,0) as chk
from sub
outer apply(
select * from sub as sub3
where sub.no > sub3.no
and(
sub.ColA = sub3.ColB
or sub.ColB = sub3.ColB
or sub.ColA = sub3.ColB
)
) as B)
select distinct
(select count(*) from sub2 as s
where s.chk = '0'
and sub2.no >= s.no)grpno,
ColA from sub2
order by grpno,ColA
;
你可以参考一下。看看。
这个难题,如果不使用循环去解决?期待你的回复。谢谢。
[/quote][/quote]with tbl1 as
(
select 'A' as c1,'A' as c2 union all
select 'A' as c1,'B' as c2 union all
select 'B' as c1,'C' as c2 union all
select 'B' as c1,'D' as c2 union all
select 'E' as c1,'F' as c2 union all
select 'G' as c1,'H' as c2 union all
select 'G' as c1,'F' as c2 union all
select 'J' as c1,'D' as c2 union all
select 'K' as c1,'C' as c2
),tab2 as(
select * from tbl1 where c1<=c2
union all
select c2,c1 from tbl1 where c1>c2
),list as(
select groupcode=ROW_NUMBER()over(order by c1),c1,c2
from tab2
where c1 not in(select c2 from tab2) or c1=c2
union all
select b.groupcode,a.c1,a.c2
from tab2 a
inner join list b on b.c2=a.c1 and a.c1<>a.c2
)
select groupcode,c1
from list
union
select groupcode,c2
from list
groupcode c1
-------------------- ----
1 A
1 B
1 C
1 D
1 J
1 K
2 E
2 F
2 G
2 H