请教一个SQL查询算法, 请大神帮忙

icelovey 2018-08-17 07:55:04

我有如下的示例数据
字段1 字段2
A A
A B
B C
B D
E F
G H
G F
J D
K C

我想遍历上述数据, 并将内容分组, 结果如下
比如, 从上表我们可以的得到A关联了AB, B又关联了CD, CD又分别关联了JK. 所以得出ABCDJK为一组.
最终结果如下
组号分组内容
1 A
1 B
1 C
1 D
1 J
1 K
2 E
2 F
2 G
2 H

...全文

1307 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

楊佳易 2019-01-04

打赏
举报

顶一下，有没有高手来解决？？

砸死牛顿的苹果 2018-12-27

打赏
举报

有点像无向图的结点集合

楊佳易 2018-12-27

打赏
举报

我在RELATION表里建了ColA和ColB的列，里面的值都是一样的。别介意。

楊佳易 2018-12-27

打赏
举报

你好。谢回复。首先，在


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
;

你可以参考一下。看看。这个难题，如果不使用循环去解决？期待你的回复。谢谢。

引用 8 楼 Dear SQL 的回复:

怎么加A和G?发出来看下 [quote=引用 5 楼 weixin_44238511 的回复:] 如果在最后一条记录上加上A和G的话，你这个SQL会分成2组，有Bug。 [quote=引用 4 楼 Dear SQL 的回复:]

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

[/quote][/quote]

楊佳易 2018-12-27

打赏
举报

有哪一位大神来接解决？如果使用Graph Database，Edge和Node的关系，使用WHERE MATCH语句，可以找到关系。用PowerBI来表示就可。但是要使用T-SQL的一般Query的话，还是难。谁能不使用循环来接写这个T-SQL？等高手。

楊佳易 2018-12-24

打赏
举报

引用 3 楼 weixin_41779699 的回复:

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
),
tbl2
as
(select ROW_NUMBER() over (order by case when c1<c2 then c1 else c2 end ) rid, * from tbl1 where c1 <> c2) --Add unique identity
,tbl3
as
(select rid,c1 from tbl2
union all
select rid,c2 from tbl2)
,tbl
as
(
select ROW_NUMBER() over (order by c1) bid,*
from (select a.c1 ,min(a.rid) rid from tbl3 a group by a.c1 having count(a.rid) = 1) a
where exists(select 1 from tbl3 where a.rid = rid and a.c1 < c1)
union all
select b.bid, a.c1,a.rid from tbl3 a cross apply (select bid from tbl where a.c1 = c1 and a.rid > rid or a.c1 > c1 and a.rid = rid) b
)
select distinct bid,c1 from tbl
order by bid,c1

你这个SQL也是，如果在最后加上A和G，那么，所有的关系就是1组了。有Bug。

楊佳易 2018-12-24

打赏
举报

如果在最后一条记录上加上A和G的话，你这个SQL会分成2组，有Bug。

引用 4 楼 Dear SQL 的回复:

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

Dear SQL(燊) 2018-12-24

打赏
举报

怎么加A和G?发出来看下

引用 5 楼 weixin_44238511 的回复:

如果在最后一条记录上加上A和G的话，你这个SQL会分成2组，有Bug。 [quote=引用 4 楼 Dear SQL 的回复:]

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

[/quote]

楊佳易 2018-12-24

打赏
举报

请问这个帖子，谁能写正确的答案呢？我也想了好几次，使用CROSS JOIN和OUTER APPLY，应该可以解决吧。期待高手出现。

Dear SQL(燊) 2018-12-10

打赏
举报

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

二月十六 2018-08-18

打赏
举报

这个关联从1到2算，从2到1也算？

卖水果的net 2018-08-18

打赏
举报

1、会不会出现循环引用的，如果存在，怎么一个显示规则？ 2、有没有标识出哪个是第一条数据。

weixin_41779699 2018-08-18

打赏
举报

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
),
tbl2
as
(select ROW_NUMBER() over (order by case when c1<c2 then c1 else c2 end ) rid, * from tbl1 where c1 <> c2) --Add unique identity
,tbl3
as
(select rid,c1 from tbl2
union all
select rid,c2 from tbl2)
,tbl
as
(
select ROW_NUMBER() over (order by c1) bid,*
from (select a.c1 ,min(a.rid) rid from tbl3 a group by a.c1 having count(a.rid) = 1) a
where exists(select 1 from tbl3 where a.rid = rid and a.c1 < c1)
union all
select b.bid, a.c1,a.rid from tbl3 a cross apply (select bid from tbl where a.c1 = c1 and a.rid > rid or a.c1 > c1 and a.rid = rid) b
)
select distinct bid,c1 from tbl
order by bid,c1